Quite often I find myself wanting to get “the latest” result from a relational table. Normally this requires a subquery, which is slow. If I want more than one column from the relational table, I have to make multiple subqueries against a single table, which slows things down even more. However, recently, I discovered a new method: joining against a subquery.
Say I have a list of events, and each one has a status report against it. In my old scheme, I would have done something like this:
SELECT e.id, e.title, e.date,
( SELECT TOP 1 r.Author FROM reports WHERE r.eventID = e.ID ORDER BY Date desc ) as ReportAuthor
( SELECT TOP 1 r.Date FROM reports WHERE r.eventID = e.ID ORDER BY Date desc ) as ReportDate
( SELECT TOP 1 r.Status FROM reports WHERE r.eventID = e.ID ORDER BY Date desc ) as ReportStatus
FROM Events e
However, the new way that I’ve found of doing things is:
SELECT e.id, e.title, e.date, r.author as ReportAuthor, r.date as ReportDate, r.Status as reportStatus
FROM Events e
LEFT JOIN (
SELECT eventID, ReportDate, Author, Status
FROM Reports r1
WHERE ReportDate = (
SELECT TOP 1 ReportDate
FROM Reports R2
WHERE r1.eventID = R2.eventID
ORDER BY reportDate desc
)
ORDER BY Date desc
) r ON r.eventID = e.ID
WHERE ….
The second form executes significantly faster in the tests that I’ve done,
