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.