Getting the "latest" related record from a SQL DB

[Gareth Harper]

I would have also used a subselect (as it's easier to understand) if it
were not for the case that 90% of the work I do is on MySQL and it is
notoriously horrible at optimising subselects (something to do with only
one key being capable of being used per table).  It may have improved in
some more recent versions but I tend to avoid subselects and try and go
with joins wherever possible.

On 9 October 2014 22:36, Abigail <abigail at abigail.be> wrote:

> 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
>