Getting the "latest" related record from a SQL DB
bill+london.pm at blunn.org
Fri Oct 10 08:27:31 BST 2014
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:
-- Or whatever fields you like
JOIN album subject_album
ON subject_album.artist_id = artist.id
LEFT JOIN album later_album
later_album.artist_id = subject_album.artist_id
AND later_album.release_date > subject_album.release_date
later_album.artist_id IS NULL
-- AND any other conditions you like
This should work for the case where an artist has released two albums at
the same time.
More information about the london.pm