Group by id and select most recent

Question

I have a table example like this:

 date        id     status
 01/01/2013  55555  high 
 01/01/2014  55555  low 
 01/01/2010  44444  high
 01/01/2011  33333  low

I need in order: group by id and select most recent date.

this is the result I want.

 date        id     status
 01/01/2014  55555  low 
 01/01/2010  44444  high
 01/01/2011  33333  low

I do not care the order of the rows.

@lurker: OP does not want any status, but the one from the last date — juergen d, Sep 19 '14 at 16:31
Select date, id, status from t1, (Select id, max(date) maxdate group by id) as g1 where t1.id = g1.id and t1.date = g1.maxdate — Ed Mendez, Sep 19 '14 at 16:33
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Barranka, Sep 19 '14 at 16:38
@juergend under the OPs' *this is the result I want*, one of the columns is `status`. — lurker, Sep 19 '14 at 16:48
@GordonLinoff and that's why I made it a comment (as a notional thing to try) rather than an answer. I wasn't sure if it would work. I use MySQL, so it worked there, as you noted it would. — lurker, Sep 19 '14 at 17:03

Barranka · Accepted Answer · 2014-09-23T18:46:20.893

3

you need to join your table with a subquery that "links" the record date with the greatest date for each id:

select a.*
from your_table as a
     inner join (
         select id, max(date) as max_date 
         from your_table 
         group by id
     ) as b on a.id = b.id and a.date = b.max_date;

edited Sep 23 '14 at 18:46

answered Sep 19 '14 at 16:36

Barranka

20,547
13
65
83

thanks to all and especially to BARRANKA . I applied your solution and works well. – eric Sep 23 '14 at 16:20

score 2 · Answer 2 · answered Sep 19 '14 at 16:37

I think you will need a subquery to get the MAX(Date) and then inner join. Try this:

SELECT A.[Date], A.[Id], A.[Status]
FROM Table A
INNER JOIN(SELECT Id, MAX([Date]) AS MaxDate
FROM Table
GROUP BY [Id]) B ON
A.[Id] = B.[Id] AND
A.[Date] = B.[MaxDate]

JohnLBevan · Answer 3 · 2014-09-19T19:41:05.737

--return the group id and the latest date in that group

select id
, MAX([date]) [latestDateInGroup]
from tbl 
group by id

--return the group id, and the related status and date for the record with the latest date in that group

select id
, [status] [latestDateInGroup'sStatus]
, [date] [latestDateInGroup]
from
(
    select id
    , [status]
    , [date]
    , row_number() over (partition by id order by [date] desc) r
    from tbl
) x
where x.r = 1

--return all ids and statuses, along with the latest date in that group's group (requires SQL 2012+)

select id
, [status]
, max([date]) over (partition by id order by [date] desc) [latestDateInGroup]
from tbl

SQL Fiddle's offline at the moment; once back up the following code should allow you to build a table to test the above queries with http://sqlfiddle.com

create table tbl ([date] date, id bigint, [status] nvarchar(4))
go

insert tbl select '2013-01-01', 55555, 'high'
insert tbl select '2014-01-01', 55555, 'low' 
insert tbl select '2010-01-01', 44444, 'high'
insert tbl select '2011-01-01', 33333, 'low'

Group by id and select most recent

3 Answers3