How to use COALESCE in WHERE in SQL?

coalesce sql oracle
sql server coalesce multiple rows
sql coalesce vs isnull
case and coalesce in sql
coalesce postgres
coalesce mysql
coalesce bigquery
coalesce sas

In the scenario where I have a dataset like so, and I need a Coalesce as to speak, within the WHERE clause.

The requirement is to filter where if there is a row with Type Actual, filter out the type of Expected. Please see dataset below which should make things clearer

Table Parent Record (GUID), Type (text), Date (datetime)

Actual Data

  1. 100, ABC Active, 01/01/2010
  2. 100, ABC Draft, 01/01/2010
  3. 100, DEF Draft, 01/01/2010
  4. 100, GHI Active, 01/01/2010
  5. 100, GHI Draft, 01/01/2010

Expected Results

  1. 100, ABC Active, 01/01/2010
  2. 100, DEF Draft, 01/01/2010
  3. 100, GHI Active, 01/01/2010

As you can see, where there is a row with type Active, for a certain type, then the Draft is removed. If there is no type with an Active row, then just show the Draft.

could someone please help with the WHERE clause for this

I think you need conditional ordering :

select top (1) with ties t.*
from table t
order by row_number() over (partition by ParentRecord, col
                            order by (case when Type = 'Active'
                                           then 1 
                                           else 2
                                      end)
                           );

This assumes type column doesn't have a other data like you have posted.

SQL Server COALESCE() Function, that accepts an expression such as SELECT , WHERE , GROUP BY , and HAVING . SELECT firstName +' '+MiddleName+' '+ LastName FullName FROM Person.Person. Let us handle the NULL values using a function called SQL COALESCE. It allows handling the behavior of the NULL value. So, in this case, use the coalesce SQL function to replace any middle name NULL values with a value ‘ ‘ (Char (13)-space).

You want filtering . . . but I don't see where coalesce() comes in. One way to do what you want is:

select t.*
from t
where t.type = 'Active'
union all
select t.*
from t
where t.type = 'Draft' and
      not exists (select 1 from t t2 where t2.guid = t.guid);

You can express this in a single query as:

select t.*
from t
where (t.type = 'Active') or
      (t.type = 'Draft' and
       not exists (select 1 from t t2 where t2.guid = t.guid)
      );

Pragmatic Guide to SQL Server COALESCE Expression, function returns the value of one of the expressions in the list. The SQL Coalesce function receives a list of parameters that are separated by commas. The function returns the value of the first of its input parameters that is not NULL. The number of parameters you pass to COALESCE is up to you. The parameters passed to Coalesce do not have to be string data types, they can be any data type and can also be different data types. The syntax is as follows.

It would be simpler if the 'Active' and 'Draft' status were stored in a separate column since this would avoid taking sub-strings in the Type column. There's actually not much in the WHERE clause.

Using a temp table for example:

DECLARE @temp TABLE (GUID int, Type varchar(100), Date datetime)
INSERT INTO @temp VALUES
 (100, 'ABC Active', '01/01/2010'), (100, 'ABC Draft', '01/01/2010')
,(100, 'DEF Draft', '01/01/2010'), (100, 'GHI Active', '01/01/2010')
,(100, 'GHI Draft', '01/01/2010'), (100, 'JKL Active', '01/01/2010')

SELECT T.GUID
      ,COALESCE( (SELECT T2.Type
                    FROM @temp T2
                   WHERE T2.GUID = T.GUID
                     AND T2.Type LIKE '%Active'
                     AND LEFT(T.Type, 3) = LEFT(T2.Type, 3)
                  ) 
                   , T.Type
                ) AS [Type]
      ,T.Date
  FROM @temp T
 WHERE T.Type LIKE '%Draft'

Produces the expected output:

GUID    Type         Date
100     ABC Active   2010-01-01 00:00:00.000
100     DEF Draft    2010-01-01 00:00:00.000
100     GHI Active   2010-01-01 00:00:00.000

SQL Server: COALESCE Function, How do you get comma separated values in SQL using coalesce? SET ANSI_NULLS OFF DECLARE @MiddleName VARCHAR(20); SET @MiddleName = NULL; SELECT * FROM [Customer] WHERE [LastName] = 'Torres' AND [MiddleName] = COALESCE(@MiddleName, [MiddleName]) When I run this query I need to get one row back because one Torres has NULL in the [MiddleName] column. But the query returns zero rows.

COALESCE, This tutorial shows you how to use the SQL Server COALESCE expression to deal with NULL in queries. SELECT id, product_name, price, discount, (price - CASE WHEN discount IS NOT NULL THEN discount ELSE 0 END) AS net_price FROM products; The query returns the same result as the one that uses the COALESCE function. In this tutorial, you have learned how to use the SQL COALESCE function to handle NULL values in the database table.

COALESCE (Transact-SQL), Transact-SQL reference for COALESCE, which returns the value of the first To ensure stable results are returned, use the SNAPSHOT  In SQL, you can use a CASE expression to change the contents of a table field from a definite value to a null value. The null value indicates that you no longer know the field’s value. Consider the following example. Imagine that you own a small airline that offers flights between Southern California and Washington state.

SQL COALESCE Function and NULL, Use SQL COALESCE to replace NULL with another value. This builit-in function returns the first non null value from a list. If you want to print their complete names as single strings, here's how to do it with COALESCE (): SELECT FirstName + ' ' +COALESCE (MiddleName,'')+ ' ' +COALESCE (LastName,'') If you don't want to

Comments
  • Apologies - update. Actual > Active, Expected > Draft
  • edit your question if you want to edit/change/augment it.
  • It's generally better to use AND/OR constructions instead of coalesce, case etc in the WHERE clause.
  • Give us some sample table data and the expected result - as formatted text, not images. Also show us your current query attempt.-
  • I cannot tell what the columns are in your sample data.