rprecioso rprecioso - 4 months ago 9
SQL Question

SQL Rank while keeping time sorted

Given this data

Type Time Outcome Wanted Result
1 8:00 1 1
1 9:00 1 1
1 10:00 1 1
0 11:00 2 2
0 12:00 2 2
0 13:00 2 2
1 14:00 1 3
1 15:00 1 3
0 16:00 2 4
1 17:00 1 5
0 18:00 2 6
1 19:00 1 7


The third column is the current result I have using the following sql query:

SELECT Type, Time, DENSE_RANK() OVER (ORDER BY Type) as Outcome
FROM Tbl


I need the dense_rank to keep the time sorted while ranking the data by type. The 4th column is the wanted result.

I am using SQL Server 2008

Answer

This is tricky. You can do this with a difference of row numbers. This method is a little difficult to understand at first, so I recommend that you run the subqueries to understand what is happening. You'll quickly "get it" when you look at the results:

select t.*,
       dense_rank() over (order by mintime) as desired_column
from (select t.*,
             min(time) over (partition by id, seqnum_t - seqnum_it) as mintime
      from (select t.*, 
                   row_number() over (order by time) as seqnum_t,
                   row_number() over (partition by id order by time) as seqnum_it
            from tbl t
           ) t
     ) t;
Comments