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.

What if multiple books are checked out on the same day? Is there a tie breaker condition? — LittleBobbyTables - Au Revoir
– LittleBobbyTables - Au Revoir, Commented Oct 11, 2010 at 15:22
@LittleBobbyTables Good question. Luckily this is not a real scenario, so I don't have to actually think about that haha. In my real scenarios, I'm usually just getting the most recent resource, and if there are two on the same date, that's either a bug somewhere else or I don't care. — Jon Smock
– Jon Smock, Commented Oct 11, 2010 at 17:27

Thomas · Accepted Answer · 2010-10-11 15:49:52Z

13

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

answered Oct 11, 2010 at 15:49

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jon Smock Over a year ago

I like the CTE; I may use one, depending on the query. I wish I could give out two accepted answers.

Jon Smock Over a year ago

Actually I'm switching the accepted answer to you, since I think the CTE solution works much more nicely. Thanks!

OMG Ponies Over a year ago

@Jon Smock: There's no performance improvement to using a CTE, but it is cleaner to read.

Jon Smock Over a year ago

@OMG Ponies That's basically why I switched. I actually started with your solution, but once I got it working, the first thing I did was rewrite it with the CTE to clean it up. Like I said, I really wish I could give two accepted answers, especially since you both submitted with minutes of each other. I did give you a plus 1 :-P

Thomas Over a year ago

@Jon Smock, @OMG Ponies - Ty for the upvote. As OMG Ponies said, there is no perf difference. It's just a bit more readable.

A-K · Accepted Answer · 2010-10-11 15:53:42Z

10

On 2005 and higher, OUTER APPLY is your friend:

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

answered Oct 11, 2010 at 15:53

A-K

17.1k8 gold badges58 silver badges77 bronze badges

Comments

OMG Ponies · Accepted Answer · 2010-10-11 15:46:28Z

3

Use:

   SELECT s.*,
          x.bookname,
          x.bookauthor,
          x.datecheckedout
     FROM TSTUDENTS s
LEFT JOIN (SELECT bc.studentid,
                  bc.bookname,
                  bc.bookauthor,
                  bc.datecheckedout,
                  ROW_NUMBER() OVER(PARTITION BY bc.studentid
                                        ORDER BY bc.datecheckedout DESC) AS rank
             FROM TSBOOKCHECKOUTS bc) x ON x.studentid = s.id
                                       AND x.rank = 1

If the student has not checkout any books, the bookname, bookauthor, and datecheckedout will be NULL.

answered Oct 11, 2010 at 15:46

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Comments

littlegreen · Accepted Answer · 2010-10-11 16:09:53Z

1

The answer of OMGPonies is a good one. I would write it with Common Table Expressions for readability:

WITH CheckoutsPerStudentRankedByDate AS (
    SELECT bookname, bookauthor, datecheckedout, studentid,
        ROW_NUMBER(PARTITION BY studentid ORDER BY datecheckedout DESC) AS rank
    FROM TSBOOKCHECKOUTS
)
SELECT 
    s.*, c.bookname, c.bookauthor, c.datecheckedout
FROM TSTUDENTS AS s
LEFT JOIN CheckoutsPerStudentRankedByDate AS c
    ON s.studentid = c.studentid
    AND c.rank = 1

The c.rank = 1 can be replaced by c.rank IN(1, 2) for last 2 checkouts, BETWEEN 1 AND 3 for last 3, etc...

answered Oct 11, 2010 at 16:09

littlegreen

7,47010 gold badges48 silver badges52 bronze badges

Comments

Dennis Xavier · Accepted Answer · 2018-08-23 06:35:02Z

Hope this is what you are looking for, a simple way which I know for these case

SELECT (SELECT TOP 1 BookName 
                 FROM TBookCheckouts 
                WHERE StudentID = TStudents.ID 
             ORDER BY DateCheckedOut DESC)[BOOK_NAME],
   (SELECT TOP 1 BookAuthor 
                   FROM TBookCheckouts 
                  WHERE StudentID = TStudents.ID 
               ORDER BY DateCheckedOut DESC)[BOOK_AUTHOR],
   (SELECT TOP 1 DateCheckedOut 
                     FROM TBookCheckouts 
                     WHERE StudentID = TStudents.ID 
                 ORDER BY DateCheckedOut DESC)[DATE_CHECKEDOUT]

This is how I solved when I faced problem like this, I think this would be the solution for your case.

p.campbell · Accepted Answer · 2010-10-11 15:22:24Z

0

If you wanted to get into using a Common Table Expression, you could the following query. It doesn't gain you anything, in this case, but for future:

;with LatestBookOut as 
(
    SELECT  C.StudentID, BookID, Title, Author, DateCheckedOut AS BookCheckout 
    FROM    CheckedOut AS C
    INNER JOIN ( SELECT StudentID, 
                        MAX(DateCheckedOut) AS DD 
                FROM Checkedout 
                GROUP BY StudentID) StuMAX                 
    ON StuMAX.StudentID = C.StudentID 
    AND StuMAX.DD = C.DateCheckedOut  
)

SELECT    B.BookCheckout,
        BookId, 
        Title,    
        Author, 
        S.*

FROM    LatestBookOut AS B
INNER JOIN Student  AS S ON S.ID = B.StudentID

edited Oct 11, 2010 at 15:22

answered Oct 11, 2010 at 15:04

p.campbell

101k70 gold badges264 silver badges326 bronze badges

2 Comments

Jon Smock Over a year ago

Won't this only return one record total? I'm looking for 1 record per student.

p.campbell Over a year ago

@Jon: here's an update. I've kept the same names from my test database, and not used the same names, as it appears you're obfuscated/mocked in your question. Hope this helps!

nang · Accepted Answer · 2010-10-11 15:40:48Z

0

create table BookCheckout(StudentID int, CheckoutDate date, BookName varchar(10))

insert into BookCheckout values (1, '1.1.2010', 'a');
insert into BookCheckout values (1, '2.1.2010', 'b');
insert into BookCheckout values (1, '3.1.2010', 'c');
insert into BookCheckout values (2, '1.1.2010', 'd');
insert into BookCheckout values (2, '2.1.2010', 'e');

select *
from BookCheckout bc1
where CheckoutDate = (
    Select MAX(CheckoutDate) 
    from BookCheckout bc2
    where bc2.StudentID= bc1.StudentID)

StudentID    CheckoutDate    BookName
2    2010-01-02    e
1    2010-01-03    c

Just add the join to TStudent and you are done. There is 1 problem left: You get multiple BookCheckouts per student if there are 2 or more Bookcheckouts for a Student with the same, max checkout date.

  select s.*, LastBookCheckout.*
  from TStudent s, 
    (select *
    from BookCheckout bc1
    where CheckoutDate = (
        Select MAX(CheckoutDate) 
        from BookCheckout bc2
        where bc2.StudentID= bc1.StudentID)) LastBookCheckout
  where s.ID = LastBookCheckout.StudentID

To avoid duplicates:

select * 
from (
  select *, RANK() over (partition by StudentID order by CheckoutDate desc,BookName) rnk
    from BookCheckout bc1) x
where rnk=1

I used "BookName" as second ordering criterion. => Use primary key instead to make it a real unique criterion.

edited Oct 11, 2010 at 15:40

answered Oct 11, 2010 at 15:23

nang

4316 silver badges16 bronze badges

4 Comments

LittleBobbyTables - Au Revoir Over a year ago

That really doesn't solve his problem as you state

You get multiple BookCheckouts per student if there are 2 or more Bookcheckouts for a Student with the same, max checkout date.

nang Over a year ago

@LittleBobbyTables: Added another solution without duplicates

LittleBobbyTables - Au Revoir Over a year ago

But it's missing students that have never checked out a book

nang Over a year ago

I considered adding a left join with TStudent obvious.

Zephyr · Accepted Answer · 2010-10-11 16:18:33Z

0

Try

    ;WITH LatestCheckouts
    AS
    (
        SELECT  DISTINCT
                A.StudentID
            ,   A.BookName   
            ,   A.BookAuthor
            ,   A.DateCheckedOut
        FROM    TBookCheckouts A
            INNER JOIN
        (   
            SELECT  StudentID
            ,   DateCheckedOut =  MAX(DateCheckedOut)
             FROM TBookCheckouts
            GROUP  BY
                StudentID
        ) B

        ON A.StudentID = B.StudentID
        AND A.DateCheckedOut =  B.DateCheckedOut
    )       
    SELECT students.*
        ,  BookName     = checkouts.BookName
        ,  BookAuthor   = checkouts.BookAuthor
        ,  BookCheckout = checkouts.DateCheckedOut

    FROM    TStudents students
        LEFT JOIN
         LatestCheckouts checkouts
    ON  students.ID = checkouts.StudentID

edited Oct 11, 2010 at 16:18

answered Oct 11, 2010 at 15:01

Zephyr

7,8634 gold badges40 silver badges42 bronze badges

4 Comments

Jon Smock Over a year ago

Close, but this won't give me the TOP 1 part. I need one record per student.

Zephyr Over a year ago

@LittleBobbyTables, fixed the compilation errors. I believet this should now work.

LittleBobbyTables - Au Revoir Over a year ago

The query returns the student multiple times if they have multiple books checked out in the same day.

Zephyr Over a year ago

Yes, it needed a DISTINCT which I've added. But some of the other solutions are more compact, so really just for the record.

Collectives™ on Stack Overflow

SELECT with multiple subqueries to same table

8 Answers 8

5 Comments

Comments

Comments

Comments

Comments

2 Comments

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

5 Comments

Comments

Comments

Comments

Comments

2 Comments

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related