Posted by Erland Sommarskog on 09/30/16 12:00

t8ntboy (t8ntboy@gmail.com) writes:
> I have two tables, SAFs (which contains course information) and
> SAF_Status (which contains records of when and how a course in the SAF
> table is modified). Many courses have multiple entries in the Status
> table. I am trying to create a query that will show all of the
> courses in SAF but only with the most recent status (i.e.,
> Max(ActionDate)).
>
>
> The joined tables contains something like this:
>
> CourseID Subj. Number. ActionDate
> 123 BIOL 101 1/8/2008
> 123 BIOL 101 12/2/2007
> 120 BIOL 123 5/5/2007
> 231 BIOL 321 2/6/2007
> 120 BIOL 123 6/23/2007
>
> What I want is:
>
> CourseID Subj. Number. ActionDate
> 123 BIOL 101 1/8/2008
> 120 BIOL 123 6/23/2007
> 231 BIOL 321 2/6/2007

WITH numbered (CourseID, Subj, Number, ActionDate, rowno) AS
SELECT CourseID, Subj, Number, ActionDate,
row_number() OVER (PARTITION BY CourseID ORDER BY ActionDate DESC)
FROM tbl
)
SELECT CourseID, Subj, Number, ActionDate
FROM numbered
WHERE rowno = 1

This solution requires SQL 2005. (Please next time specify which version
of SQL Server you are using.)

On SQL 2000 you can do:

SELECT a.CourseID, a.Subj, a.Number, a.ActionDate
FROM tbl a
JOIN (SELECT CourseID, ActionDate = MAX(ActionDate)
FROM tbl
GRUOP BY CourseID) AS b ON a.CourseID = b.CourseID
AND a.ActionDate = b.ActionDate


I leave it as an exercise to fit this into your current query.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]