CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlgreatest-n-per-group
M99
M99

Reputation: 1907

SQL Server - How to display most recent records

I have a table in SQL Server. 

ID PID LASTMODIFIED REASON
1  1   01/01/2011   XYZ
2  1   04/01/2011   XYY
3  2   05/01/2011   ZZZ
4  2   03/01/2011   ABC

I want to select the rows based on MAX(LASTMODIFIED) for each PID. For EX, the select should return the following:

ID PID LASTMODIFIED REASON
2  1   04/01/2011   XYY
3  2   05/01/2011   ZZZ

Pelase help me with the SQL statement. Thank you.

Upvotes: 0

Views: 700

Answers (1)

Dylan Smith
Dylan Smith

Reputation: 22255

SELECT A.ID, A.PID, A.LastModified, A.Reason 
FROM MyTable AS A INNER JOIN (
SELECT PID, MAX(LastModified) AS MaxDate
FROM MyTable
GROUP BY PID) AS B ON A.PID = B.PID AND A.LastModified = B.MaxDate

Upvotes: 4

Related Questions