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
