Getting the "latest" related record from a SQL DB

[Abigail]

On Thu, Oct 09, 2014 at 01:28:44PM +0100, Andrew Beverley wrote:
> Hi guys,
> 
> I'm after some best-practice advice regarding SQL database design.
> 
> I have a table (say "artist", couldn't resist...) that has a one-to-many
> relationship to another table (say "album"). The album table has a field
> which references the artist table's ID. So one artist can have many
> albums.
> 
> So, if I want to know all of an artist's albums, that's easy.
> 
> But what if I want to fetch an artist's details and his latest album? I
> can select the artist from the artists table and then join the albums
> table. But to get the latest album I'd have to use a max function (say
> on the album's date), with which it isn't possible to get the related
> fields in the same row.
> 
> I see 2 ways of solving this:
> 
> - Run multiple queries to get the relevant album's ID (if even possible)
> and then retrieve its row in entirety.
> 
> - Have a reference from the artist table back to the album table,
> specifying which is the latest album, which I update each time the
> albums table is updated.
> 
> Neither seem particularly tidy to me, so am I missing something
> completely obvious?
> 


Something like this (untested):

   SELECT  Artist.*, Album.name
     FROM  Artist, Album
    WHERE  Artist.id = Album.artist_id
      AND  Artist.id = ?
      AND  Album.release_date = (SELECT  MAX(Album.release_date)
                                   FROM  Artist, Album
                                  WHERE  Artist.id = Album.artist_id
                                    AND  Artist.id = ?);


For the placeholders, you supply the artist id you are interested in.
If you leave the "Artist.id = ?" clauses off, you get the latest
release of all the artists.

This assumes no artist releases two albums at the same time.


Abigail