CODEWITHSUNDEEP
sqlsql-servercoalesce
Pat Migliaccio
Pat Migliaccio

Reputation: 1070

Simplify COALESCE query

I'm looking to simplify the query below and I just can't wrap my head around an alternate solution but I think there must be one available.

The objective is to retrieve Col_D from Col_C. If Col_D returns NULL, I want to get Col_D from Col_B. If that returns NULL, once again go up the ladder and get Col_D from Col_A. But only where Col_B and Col_C are NULL.

Tables

Table_A
Col_A   Col_B   Col_C   Col_X
L000    3200    3220    0000

Table_B
Col_A   Col_B   Col_C   Col_D
L000    NULL    NULL    3256
L000    3200    NULL    6483
L000    3200    3210    7213
L000    3200    3220    NULL
L000    3200    3230    9462

Query

DECLARE @X nvarchar(4) = '0000'

DECLARE @A nvarchar(4), @B nvarchar(4), @C nvarchar(4)
SELECT @A = Col_A, @B = Col_B, @C = Col_C FROM [Table_A] 
WHERE [Col_X] = @X

SELECT COALESCE(
(SELECT [Col_D] FROM [Table_B] WHERE Col_C = @C AND Col_B = @B AND Col_A = @A), 
(SELECT [Col_D] FROM [Table_B] WHERE Col_C IS NULL AND Col_B = @B AND Col_A = @A), 
(SELECT [Col_D] FROM [Table_B] WHERE Col_C IS NULL AND Col_B IS NULL AND Col_A = @A)
) AS Col_D

Expected Output

Col_D
6483

Edit: Added Table_A and Expected Output

Upvotes: 0

Views: 126

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

SQL Server sorts nulls last when ordering descending. So all you have to do is get all possible candidate records, which is: col_d must not be null, col_a must match, and col_b and col_c must match or be null. Order descending and take the top row.

select col_d
from table_b
where col_d is not null
and col_a = @a
and (col_b = @b or col_b is null)
and (col_c = @c or col_c is null)
order by col_b desc, col_c desc;

Full query including table_a:

select b.col_d
from (select * from table_a where col_x = @x) a
join table_b b on  b.col_d is not null
               and b.col_a = a.col_a
               and (b.col_b = a.col_b or b.col_b is null)
               and (b.col_c = a.col_c or b.col_c is null)
order by b.col_b desc, b.col_c desc;

Upvotes: 1

Michael L.
Michael L.

Reputation: 618

Gordon may have answered before you clarified intent by providing expected output. Note that my query is not logically identical to your original query. It will return the row with the fewest nulls where Col_A = @A. For instance, your query would exclude results where Col_C and Col_A have values, but Col_B is null, this query would not. Also, if there are no rows where Col_A = @A, it will return an empty result set instead of a NULL value.

If the new query must be logically identical to the original query, then I'm not sure if it could be simplified in terms of being easier to read, though there may be a more "complicated" solution that performs better.

SELECT TOP 1 Col_D
FROM Table_B
WHERE Col_A = @A
ORDER BY CASE WHEN Col_B IS NOT NULL -- Sort null Col_B values to the bottom
                THEN 1 
                ELSE 0 
         END,
         CASE WHEN Col_C IS NOT NULL -- Sort null Col_C values to the bottom
                THEN 1 
                ELSE 0 
         END,
         CASE WHEN Col_D IS NOT NULL -- Sort null Col_D values to the bottom
                THEN 1 
                ELSE 0 
         END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You want one row, prioritized. So:

Select cold
From t
Where (cold = @a or colc is null) and
       (Colb = @b or colb is null)
Order by (case when colc is not null then 1 else 2 end),
         (Case when colc is not null then 1 else 2 end)
Fetch first 1 row only;

Depending on the database, you might use limit or top to get one row.

Upvotes: 0

Related Questions