MSSQL: Returning the last record for each member of a group

[Phil99]

I have two tables; statement and rep. Each rep has many statements. I want to create a view where the latest statement for each rep is displayed in a list but can't quite work out how to do it. TOP or MAX(statementID) would work if I wanted the latest statement out of the lot, but I need to have them displayed in the group. The definition of latest in this case is the highest statementID per rep.

Any ideas? I've had a search on Google but haven't found exactly what I'm after, at least in a way I can understand

Thanks

 

[SimonCHere]

Assuming your tables are:

Rep:
RepID
RepText
...

Statement:
StatementID
RepID
StatementDetails
...

SELECT r.RepId, r.RepText, s.StatementDetails
FROM Statement s
INNER JOIN Rep r
  ON r.RepId = s.RepId
INNER JOIN (
                SELECT s.RepId, MAX(s.StatementId) sid 
                FROM Statement s 
                GROUP BY s.RepId) s1
  ON s1.sid = s.StatementId
  AND r.RepId = s1.RepId

No checks for reports without statements or vice versa.

Simon

 

[Phil99]

Works perfectly, thank you very much!

 

[SimonCHere]

No problem.