Getting latest record from related table

Transact-SQL
1

I have a question which I'll try to illustrate here using the typical "Authors -> Books" relationship. Many "book" records can be related to an "author" record. To the Books table I've added a "PublishedUTC" column, which is the date of publication. What I'm trying to do is, for each Author, get some data from their latest published Book.

Normally I might use a subquery in the SELECT, but that's only good to retrieve 1 column, whereas I want to retrieve multiple columns from the Book table for that latest book record. The SQL below seems to half-work, in that it only ever returns 1 book record for the entire set, instead of 1 book record for each author:

Declare @Authors table
(
  AuthorId int,
  Name varchar(100)
);

Declare @Books table
(
  BookId int,
  AuthorId int,
  PublishedUTC date,
  Title varchar(100),
  TotalPages int
);

INSERT INTO @Authors (AuthorId, Name) VALUES (1, 'Dave Dobbins');
INSERT INTO @Authors (AuthorId, Name) VALUES (2, 'Edgar Evans');

INSERT INTO @Books (BookId, AuthorId, PublishedUTC, Title, TotalPages) VALUES (1, 1, '2001-01-01', 'Stewards of Gondor', 245);
INSERT INTO @Books (BookId, AuthorId, PublishedUTC, Title, TotalPages) VALUES (2, 1, '2010-02-02', 'Heralds of Highgarden', 71);
INSERT INTO @Books (BookId, AuthorId, PublishedUTC, Title, TotalPages) VALUES (3, 2, '2010-03-03', 'Tales of Trombones', 1244);

SELECT a.AuthorId, a.Name, b.LatestTitle, b.TotalPages
  FROM @Authors a
  LEFT OUTER JOIN 
  (SELECT TOP 1 AuthorId, Title AS LatestTitle, TotalPages
    FROM @Books
    ORDER BY PublishedUTC DESC) b on b.AuthorId = a.AuthorId

The result I'm looking for is this:

AuthorId Name           LatestTitle            TotalPages
   1     Dave Dobbins   Heralds of Highgarden     71
   2     Edgar Evans    Tales of Trombones        1244

But the result I get is this:

AuthorId Name           LatestTitle            TotalPages
   1     Dave Dobbins   (null)                   (null)
   2     Edgar Evans    Tales of Trombones        1244

Where am I going wrong?

2

The "top 1" will return the newest book, regardless of author.

Option 1:

select AuthorId
      ,Name
      ,Title
      ,TotalPages
  from (select a.AuthorId
              ,a.Name
              ,b.Title
              ,b.TotalPages
              ,row_number() over(partition by a.AuthorId
                                 order by b.PublishedUTC desc
                                ) as rn
          from @Authors as a
               left outer join @Books as b
                            on b.AuthorId=a.AuthorId
       ) as a
 where rn=1

Option 2:

select a.AuthorId
      ,a.Name
      ,b.LatestTitle
      ,b.TotalPages
  from @Authors as a
       left outer join (select AuthorId
                              ,Title as LatestTitle
                              ,TotalPages
                              ,row_number() over(partition by AuthorId
                                                 order by PublishedUTC desc
                                                ) as rn
                          from @Books as b
                       ) b
                    on b.AuthorId=a.AuthorId
                   and b.rn=1

Option 3:

select a.AuthorId
      ,a.Name
      ,b2.Title
      ,b2.TotalPages
  from @Authors as a
       left outer join (select AuthorId
                              ,max(PublishedUTC) as PublishedUTC
                          from @Books
                         group by AuthorId
                       ) as b1
                    on b1.AuthorId=a.AuthorId
       left outer join @Books as b2
                    on b2.AuthorId=b1.AuthorId
                   and b2.PublishedUTC=b1.PublishedUTC
1 Like
3

Wow, thank you very much indeed! I'll have to look up the "row_number() over(partition .." functions, looks very useful!

ED: The only problem I see with the third option, if I'm not mistaken, is if there is more than 1 book with the same latest PublishedUTC date, it will return more than 1 book record for that author.

4

Absolutely correct