CODEWITHSUNDEEP
sql-serversql-order-byrow-number
Alex
Alex

Reputation: 33

ROW_NUMBER on a specific query

I have the below data, and I performed a ROW_NUMBER(partition by ID order by YEAR) function based on year which's ranking my data as below. I want to bring in name for every id based on their latest year. I want to bring in NULL data if that's the only data available and bring in latest NON NULL data for every other record. But rownumber only lets me bring in recent name which could be NULL. How do I query below data to bring in most recent NON NULL name?

ID  year  name  rownum
10 2011   abc    1
10 2010   abc    2
11 2011   ghi    1
11 2010   ghi    1
13 2010   NULL   1
13 2009   jkl    2
14 2014   NULL   1
14 2014   mno    2
15 2015   NULL   1

I want to bring in names jkl, mno for ID's 13 and 14 and not NULLS in my final result. Any suggestion on how to achieve that?

The output I desire is below - I want to display data for ROW NUM=1

10 2011  abc  
11 2011  ghi
13 2009  jkl
14 2014  mno
15 2015  NULL

Upvotes: 0

Views: 81

Answers (1)

Shannon Severance
Shannon Severance

Reputation: 18410

Sort non-null rows ahead of null rows:

select ID, year, name
from (select *,
        row_number() over (partition by ID 
            order by case when name is null then 1 else 0 end, year desc) as RN
    from #t) _
where rn = 1

See also SQL Server equivalent to Oracle's NULLS FIRST?, SQL Server ORDER BY date and nulls last &

Upvotes: 1

Related Questions