CODEWITHSUNDEEP
sqlsql-server-2005greatest-n-per-group
John Beynon
John Beynon

Reputation: 37507

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.

Upvotes: 8

Views: 7981

Answers (3)

Tom H
Tom H

Reputation: 47402

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 either case, if the created date can be identical between two rows with the same type_id then you would need to account for that.

I've also assumed at least one comment per type exists. If that's not the case then you would need to make a minor adjustment for that as well.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332791

What are the actual columns you want returned?

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

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171599

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

Upvotes: 15

Related Questions