Getting the "latest" related record from a SQL DB

[William Blunn]

On 09/10/2014 13:28, Andrew Beverley wrote:
> But what if I want to fetch an artist's details and his latest album?

This requirement appears to only want the answer for a single artist, 
and the way the question is framed implies that there will be only one 
latest album for a given artist.

But I see that a couple of responders have provided solutions to 
retrieve the answer for multiple artists, and to cover the case where an 
artist has released two albums at the same time.

Abigail's approach uses a subquery.

Gianni's approach uses a window function (and it seems it would work 
even if an artist released two albums at the same time).

Another approach would be to re-frame the question as:

"For each artist, tell me about any of their albums for which they have 
made no later release."

This then gives a clue to a possible other solution involving an anti-join:

SELECT
   artist.*,
   subject_album.*
   -- Or whatever fields you like
   FROM
     artist
     JOIN album subject_album
       ON subject_album.artist_id = artist.id
     LEFT JOIN album later_album
       ON
         later_album.artist_id = subject_album.artist_id
         AND later_album.release_date > subject_album.release_date
   WHERE
     later_album.artist_id IS NULL
     -- AND any other conditions you like
     ;

(Untested)

This should work for the case where an artist has released two albums at 
the same time.

Regards,

Bill