SELECT with multiple subqueries to same table

Question

I'm using the same SQL pattern over and over, and I know there has to be a better way, but I'm having trouble piecing it together. Here's a simple version of the pattern, where I'm pulling back the student's information and the last book they checked out, if one exists:

SELECT TStudents.*,
       BookName = (SELECT TOP 1 BookName 
                     FROM TBookCheckouts 
                    WHERE StudentID = TStudents.ID 
                 ORDER BY DateCheckedOut DESC),
       BookAuthor = (SELECT TOP 1 BookAuthor 
                       FROM TBookCheckouts 
                      WHERE StudentID = TStudents.ID 
                   ORDER BY DateCheckedOut DESC),
       BookCheckout = (SELECT TOP 1 DateCheckedOut 
                         FROM TBookCheckouts 
                         WHERE StudentID = TStudents.ID 
                     ORDER BY DateCheckedOut DESC)
   FROM TStudents

(For the sake of this example, please ignore the fact that TBookCheckouts should probably be split into TCheckouts and TBooks)

What I'm trying to illustrate: I tend to have a lot of subqueries for columns from the same table. I also tend to need to sort those subqueried tables by a date to get the most recent record, so it's not quite as simple (at least to me) as doing a LEFT JOIN. Notice, though, that except for which field is being returned, I'm essentially doing the same subquery 3 times. SQL Server may be smart enough to optimize that, but I'm thinking not (I definitely need to get better at reading execution plans...).

While there might be advantages to structuring it this way (sometimes this ends up being more readable, if I have tons of subqueries and sub-tables), it doesn't seem like this is particularly efficient.

I've looked into doing a LEFT JOIN from a derived table, possibly incorporating a ROW_NUMBER() and PARTITION BY, but I just can't seem to piece it all together.

Answer 1

If you are using SQL Server 2005 and later, you can use a ranking function like so:

With LastCheckout As
    (
    Select StudentId, BookName, BookAuthor, DateCheckedOut 
        , Row_Number() Over ( Partition By StudentId Order By DateCheckedOut Desc) As CheckoutRank
    From TBookCheckouts
    )
Select ..., LastCheckout.BookName, LastCheckout.BookAuthor, LastCheckout.DateCheckedOut
From TStudents
    Left Join LastCheckout 
        On LastCheckout.StudentId = TStudents.StudentId
                And LastCheckout.CheckoutRank = 1