Select last rows for each foreign key value

Question

Sorry for the headline, I could not come up with something short and for sure ...

I have an "Updates" table with three columns, text, type created - the text is a text field, typeid is the foreign key from the "type" table, and the created one is a timestamp. The user enters the update and selects the "type" that matches him.

There is a corresponding type table with the columns "id" and "name".

I am trying to get a result with as many rows as in the "type" table, and the last value from updates.text for a particular row in the types. Therefore, if I have 3 types, 3 rows will be returned, one row for each type and the most recent update.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 

Answer 2

What are the actual columns you want to return?

 SELECT t.*, y.* FROM TYPE t JOIN (SELECT u.typeid, MAX(u.created) 'max_created' FROM UPDATES u GROUP BY u.typeid) x ON x.typeid = t.id JOIN UPDATES y ON y.typeid = x.typeid AND y.created = x.max_created 

Answer 3

 SELECT TYP.id, TYP.name, TXT.comment FROM dbo.Types TYP INNER JOIN dbo.Type_Comments TXT ON TXT.type_id = TYP.id WHERE NOT EXISTS ( SELECT * FROM dbo.Type_Comments TXT2 WHERE TXT2.type_id = TYP.id AND TXT2.created > TXT.created ) 

Or:

 SELECT TYP.id, TYP.name, TXT.comment FROM dbo.Types TYP INNER JOIN dbo.Type_Comments TXT ON TXT.type_id = TYP.id LEFT OUTER JOIN dbo.Type_Comments TXT2 ON TXT2.type_id = TYP.id AND TXT2.created > TXT.created WHERE TXT2.type_id IS NULL 

In any case, if the created date can be identical between two lines with the same id_type, you will need to consider this.

I also suggested that there is at least one comment for each type. If this is not the case, you will also need to make a minor adjustment.