Search code examples
sqlgroup-bydistinctgreatest-n-per-group

SQL Group sort by date

I have a table that resembles the following:

id
type
created (date).

What I want is to return the most recently created item of each type. So it will return one item (most recent) for each type.

E.G.

id:1 type:A created:2011

id:2 type:A created:2008

id:3 type: B created:2009

id:4 type: B created:2010

This will return with record id 1 and 4.

Solution

  • this works using a self join

    select T1.* from table t1 LEFT JOIN table t2 
ON t1.type = t2.type and t1.created < t2.created
where t2.id is null