selecting latest rows per distinct foreign key value

Question

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 1

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