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?