CODEWITHSUNDEEP
sqlsql-server
1.618
1.618

Reputation: 227

Return first not null result from a column

On SQL Server, I have the following query (minimized):

SELECT A.ID, A.OWNER, B.CAR
FROM TABLE A
LEFT JOIN TABLE B ON A.ID = B.CAR_ID

Which returns the following:

ID   Owner   Car
01   Bob     BMW
02   Bob     NULL
03   Bob     BMW
04   Andy    Audi
05   Andy    Audi

I want to Group By Owner with the first not NULL result for car to get:

Owner   Car
Bob     BMW
Andy    Audi

I could do:

SELECT A.OWNER, max(B.CAR) as Car
FROM TABLE A
LEFT JOIN TABLE B ON A.ID = B.CAR_ID
GROUP BY A.OWNER

But, is there way to do this with Coalesce()? Or something else that might work better with a more complex query?

Upvotes: 1

Views: 561

Answers (3)

Menno
Menno

Reputation: 12621

You could do the following:

SELECT TOP 1 WITH TIES A.OWNER, B.CAR
FROM TABLE A
LEFT JOIN TABLE B ON A.ID = B.CAR_ID
ORDER BY ROW_NUMBER() OVER (PARTITION BY A.OWNER ORDER IIF(B.CAR IS NOT NULL, 0, 1), A.ID)

By splitting the ORDER BY in two, you place all NULL's last, followed by ordering on the given ID in your resultset. Each distinct A.OWNER will receive ROW_NUMBER() 1. Using TOP 1 WITH TIES you're left with all 1's without using a subquery, thus having only one row per A.OWNER.

Upvotes: 1

B0RDERS
B0RDERS

Reputation: 217

I'm not sure what you mean by first result. If you want to go by the default order, you could do: If you were ordering by ID, then it would be

SELECT DISTINCT FIRST_VALUE(Owner) OVER(PARTITION BY Owner ORDER BY ID), FIRST_VALUE(Car) OVER(PARTITION BY Owner ORDER BY ID)
FROM Table_Name WHERE Car IS NOT NULL

Upvotes: 1

pwilcox
pwilcox

Reputation: 5753

When a car is present, your result-set always associates 'Bob' with 'BMD' and 'Andy' with 'Audi'. I assume, however, that in the real dataset there are owners that can have more than one type of car. So the question then becomes: "which one do you choose?".

If it's really arbitrary and doesn't matter, then your existing approach using 'max' is fine. At least it has a predictable default ordering so that you'll get the same output on every run given the same state of data in the base tables.

However, if something else should count as 'first', such as if you wanted to base the comparison on the 'id' field, then you're going to want to use 'row_number' to order by that field for within each owner, such as in the code below. 

select      owner, car
from        (
                select      *, 
                            ord = row_number() over(partition by owner order by id)
                from        [Table A] a
                left join   [Table B] b on a.id = b.car_id
                where       b.car is not null
            ) orderings
where       ord = 1

Upvotes: 1

Related Questions