Get most recent date for each id

Question

I need to get the max date for each id in this table

ID       Date
___      ____
1     2017-01-01
1     2017-03-01
2     2018-01-01
2     2013-07-03
2     2015-03-02
3     2011-11-11

So I'd want something like this returned

ID       Date
___      ____
1     2017-03-01
2     2018-01-01
3     2011-11-11

Thanks in advance

Possible duplicate of [Select info from table where row has max date](https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date) — Hasan Fathi, Jan 24 '18 at 09:11
Possible duplicate of [Select max value of each group](https://stackoverflow.com/questions/4510185/select-max-value-of-each-group) — Salman A, Jan 24 '18 at 09:45

score 3 · Accepted Answer · answered Jan 24 '18 at 09:01

3

select ID, Max(Date) as Date 
from yourtable
group by ID

answered Jan 24 '18 at 09:01

Milen

score 1 · Answer 2 · answered Jan 24 '18 at 09:01

1

Use ROW_NUMBER:

SELECT ID, Date
FROM
(
    SELECT ID, Date, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) rn
    FROM yourTable
) t
WHERE rn = 1;

Or GROUP BY:

SELECT ID, MAX(Date) AS Date
FROM yourTable
GROUP BY ID;

answered Jan 24 '18 at 09:01

Tim Biegeleisen

2 Answers2