0

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
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
Alex
  • 33
  • 9
  • As posted your question doesn't have enough detail for me to understand what you are trying to do. Can you create a sqlfiddle this? Start at sqlfiddle.com – Sean Lange Dec 17 '14 at 21:04
  • `ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [YEAR] DESC) ..... FROM ..... WHERE name IS NOT NULL` – Lamak Dec 17 '14 at 21:08
  • lamak, how would i handle this when i have an ID 15 with name as NULL? I don't want to lose NULL data regarding 15. I want to display 15 2015 NULL in my output – Alex Dec 17 '14 at 21:11
  • I tried using where name is not null which filtered out ID 15 and I want to have data regarding 15 as NULL in my output – Alex Dec 17 '14 at 21:13
  • _I want to fetch the most recent non NULL name based on year_ This line in your question seems to suggest otherwise. – GVashist Dec 17 '14 at 21:14
  • 2
    `ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [YEAR] DESC, CASE WHEN name IS NOT NULL THEN 1 ELSE 0 END)` – Lamak Dec 17 '14 at 21:15
  • lamak, what does ELSE 0 do? I applied this logic on my code and it still doesn't filter out NULL data where NON NULL data exists. – Alex Dec 17 '14 at 21:35
  • yeah, it should've been `CASE WHEN name IS NULL THEN 1 ELSE 0 END` instead – Lamak Dec 17 '14 at 21:37
  • Hmm. that's opposite of what i want! I do not want NULL values to be ranked as 1 unless that's the only record available. – Alex Dec 17 '14 at 21:40
  • it shouldn't, that was the actual result that you got? – Lamak Dec 17 '14 at 21:43
  • my results remained the same before i used the case and after i used the case! – Alex Dec 17 '14 at 21:46
  • look, dude, you just need to change the order of the conditions, that's all: `ROW_NUMBER() OVER(PARTITION BY ID ORDER BY CASE WHEN name IS NULL THEN 1 ELSE 0 END, [year] DESC)` – Lamak Dec 17 '14 at 21:52
  • Thank you, lamak! that worked. you are very kind and thanks again :) – Alex Dec 17 '14 at 22:06

1 Answers1

1

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 &

Community
  • 1
  • 1
Shannon Severance
  • 18,025
  • 3
  • 46
  • 67