Getting the "latest" related record from a SQL DB

[Andrew Beverley]

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?

Thanks,

Andy