SQL SERVER How to select the latest record in each group?

Question

| ID | TimeStamp | Item |
|----|-----------|------|
| 1  | 0:00:20   | 0    |
| 1  | 0:00:40   | 1    |
| 1  | 0:01:00   | 1    |
| 2  | 0:01:20   | 1    |
| 2  | 0:01:40   | 0    |
| 2  | 0:02:00   | 1    |
| 3  | 0:02:20   | 1    |
| 3  | 0:02:40   | 1    |
| 3  | 0:03:00   | 0    |

I have this and I would like to turn it into

| ID | TimeStamp | Item |
|----|-----------|------|
| 1  | 0:01:00   | 1    |
| 2  | 0:02:00   | 1    |
| 3  | 0:03:00   | 0    |

Please advise, thank you!

Possible duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — GSerg, Mar 24 '20 at 13:41
Well. At least 2,946 https://stackoverflow.com/questions/tagged/greatest-n-per-group — Martin Smith, Mar 24 '20 at 14:06

score 1 · Answer 1 · answered Mar 24 '20 at 13:57

A correlated subquery is often the fastest method:

select t.*
from t
where t.timestamp = (select max(t2.timestamp)
                     from t t2
                     where t2.id = t.id
                    );

For this, you want an index on (id, timestamp).

You can also use row_number():

select t.*
from (select t.*,
             row_number() over (partition by id order by timestamp desc) as seqnum
      from t
     ) t
where seqnum = 1;

This is typically a wee bit slower because it needs to assign the row number to every row, even those not being returned.

score 1 · Answer 2 · answered Mar 24 '20 at 14:06

You need to group by id, and filter out through timestamp values descending in order to have all the records returning as first(with value 1) in the subquery with contribution of an analytic function :

SELECT *
  FROM
  (
   SELECT *,
          DENSE_RANK() OVER (PARTITION BY ID ORDER BY TimeStamp DESC) AS dr
     FROM t
  ) t
  WHERE t.dr = 1

where DENSE_RANK() analytic function is used in order to include records with ties also.

SQL SERVER How to select the latest record in each group?

2 Answers2