Search code examples
sqlwindow-functions

Using the Row_Number function

My data looks like this - 

clientid    calendar    Num
18161       20170518    1
18161       20170705    0
18161       20170718    0
43431       20150518    0

The first 0 Num for 18161 client is on the 2nd calendar. The first 0 Num for 43431 client is on the 1st calendar (20150518). I want a SQL to generate this output - 

clientid    FirstZero
18161       2 
43431       1

This is what I have so far, but the row_number is being generated for all calendars. I need it just for the first time Num becomes zero for a particular client. 

SELECT clientid, calendar,
Row_Number() Over (order by clientid) As FirstZero
from DAILY
where num = 0  
and clientid = 18161
Solution

  • Here you go:

    select
  clientid,
  min(pos) as firstzero
from (
  select
    clientid,
    num,
    row_number() over(partition by clientid order by calendar) as pos
  from daily
) x
where num = 0
group by clientid