John Beynon John Beynon - 4 months ago 10
SQL Question

selecting latest rows per distinct foreign key value

excuse the title, i couldn't come up with something short and to the point...

I've got a table 'updates' with the three columns, text, typeid, created - text is a text field, typeid is a foreign key from a 'type' table and created is a timestamp. A user is entering an update and select the 'type' it corresponds too.

There's a corresponding 'type' table with columns 'id' and 'name'.

I'm trying to end up with a result set with as many rows as is in the 'type' table and the latest value from updates.text for the particular row in types. So if i've got 3 types, 3 rows would be returned, one row for each type and the most recent updates.text value for the type in question.

Any ideas?

thanks,

John.

Answer
select u.text, u.typeid, u.created, t.name
from  (
    select typeid, max(created) as MaxCreated
    from updates
    group by typeid
) mu
inner join updates u on mu.typeid = u.typeid and mu.MaxCreated = u.Created
left outer join type t on u.typeid = t.typeid