How to get the first or the last in a group with SQL Server
- Microsoft Access has 2 summary functions that are very useful:
- first()
- last()
The first() gives you, the first occurrence of a repeating event, answering the question: since. The last() gives you, the last occurrence of a repeating event, answering the question: up to. Other SQL Servers, like Oracle, provide the the first()/last() summary operations.
It's actually fairly easy to replicate that feature with the dense_rank() function.
Applies to:
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE sql911;
- go
- SELECT counter,emp_short,trans_date,debit,credit
- FROM trans02
- ORDER BY emp_short, trans_date DESC;
- go
counter emp_short trans_date debit credit ----------- --------- ----------------------- --------------------- --------------------- 130 000010 2007-07-25 00:00:00.000 33.69 NULL 120 000010 2007-07-24 00:00:00.000 22.56 NULL 110 000010 2007-07-23 00:00:00.000 11.00 NULL 70 000030 2007-07-20 00:00:00.000 NULL 29.50 60 000030 2007-07-18 00:00:00.000 NULL 41.00 50 000030 2007-07-16 00:00:00.000 NULL 22.00 40 000030 2007-07-15 00:00:00.000 357.00 NULL NULL 000070 2007-10-01 00:00:00.000 52.50 NULL NULL 000070 2007-09-27 00:00:00.000 55.00 NULL NULL 000070 2007-09-26 00:00:00.000 NULL 11.22 NULL 000070 2007-09-25 00:00:00.000 NULL 11.11 100 000110 2007-07-23 00:00:00.000 77.00 NULL 90 000110 2007-07-22 00:00:00.000 66.00 NULL 80 000110 2007-07-20 00:00:00.000 42.00 NULL 20 000110 2007-06-01 00:00:00.000 NULL 100.00 NULL 000160 2007-10-21 00:00:00.000 NULL 45.00 NULL 000160 2007-10-20 01:00:00.000 11.50 NULL NULL 000160 2007-10-20 00:00:00.000 9.00 NULL 30 000190 2007-06-02 00:00:00.000 25.00 NULL 10 000190 2007-06-01 00:00:00.000 NULL 125.00 (20 row(s) affected)
Getting the last
- SELECT counter,emp_short,trans_date,debit,credit,ranking
- FROM
- (SELECT counter,emp_short,trans_date,debit,credit,
- dense_rank() OVER (partition BY emp_short
- ORDER BY emp_short, trans_date DESC) AS ranking
- FROM trans02) AS trans
- WHERE ranking = 1;
- go
counter emp_short trans_date debit credit ranking ----------- --------- ----------------------- --------------------- --------------------- -------------------- 130 000010 2007-07-25 00:00:00.000 33.69 NULL 1 70 000030 2007-07-20 00:00:00.000 NULL 29.50 1 NULL 000070 2007-10-01 00:00:00.000 52.50 NULL 1 100 000110 2007-07-23 00:00:00.000 77.00 NULL 1 NULL 000160 2007-10-21 00:00:00.000 NULL 45.00 1 30 000190 2007-06-02 00:00:00.000 25.00 NULL 1 (6 row(s) affected)
Getting the last explanations
- SELECT counter,emp_short,trans_date,debit,credit,ranking
- FROM
- (SELECT counter,emp_short,trans_date,debit,credit,
- dense_rank() OVER (partition BY emp_short
- ORDER BY emp_short, trans_date DESC) AS ranking
- FROM trans02) AS trans;
- go
- dense_rank() returns a ranking of rows within the partition without any gaps in the ranking.
- Since we only want the 1st occurrence, I use the: where ranking = 1;
counter emp_short trans_date debit credit ranking ----------- --------- ----------------------- --------------------- --------------------- -------------------- 130 000010 2007-07-25 00:00:00.000 33.69 NULL 1 120 000010 2007-07-24 00:00:00.000 22.56 NULL 2 110 000010 2007-07-23 00:00:00.000 11.00 NULL 3 70 000030 2007-07-20 00:00:00.000 NULL 29.50 1 60 000030 2007-07-18 00:00:00.000 NULL 41.00 2 50 000030 2007-07-16 00:00:00.000 NULL 22.00 3 40 000030 2007-07-15 00:00:00.000 357.00 NULL 4 NULL 000070 2007-10-01 00:00:00.000 52.50 NULL 1 NULL 000070 2007-09-27 00:00:00.000 55.00 NULL 2 NULL 000070 2007-09-26 00:00:00.000 NULL 11.22 3 NULL 000070 2007-09-25 00:00:00.000 NULL 11.11 4 100 000110 2007-07-23 00:00:00.000 77.00 NULL 1 90 000110 2007-07-22 00:00:00.000 66.00 NULL 2 80 000110 2007-07-20 00:00:00.000 42.00 NULL 3 20 000110 2007-06-01 00:00:00.000 NULL 100.00 4 NULL 000160 2007-10-21 00:00:00.000 NULL 45.00 1 NULL 000160 2007-10-20 01:00:00.000 11.50 NULL 2 NULL 000160 2007-10-20 00:00:00.000 9.00 NULL 3 30 000190 2007-06-02 00:00:00.000 25.00 NULL 1 10 000190 2007-06-01 00:00:00.000 NULL 125.00 2 (20 row(s) affected)
Getting the first
- SELECT counter,emp_short,trans_date,debit,credit,ranking
- FROM
- (SELECT counter,emp_short,trans_date,debit,credit,
- dense_rank() OVER (partition BY emp_short
- ORDER BY emp_short, trans_date) AS ranking
- FROM trans02) AS trans
- WHERE ranking = 1;
- go
counter emp_short trans_date debit credit ranking ----------- --------- ----------------------- --------------------- --------------------- -------------------- 110 000010 2007-07-23 00:00:00.000 11.00 NULL 1 40 000030 2007-07-15 00:00:00.000 357.00 NULL 1 NULL 000070 2007-09-25 00:00:00.000 NULL 11.11 1 20 000110 2007-06-01 00:00:00.000 NULL 100.00 1 NULL 000160 2007-10-20 00:00:00.000 9.00 NULL 1 10 000190 2007-06-01 00:00:00.000 NULL 125.00 1 (6 row(s) affected)
- Getting the last is identical to getting the first, just the order of the sort, that decides which to use, either the first or the last.