Getting the "latest" related record from a SQL DB

[Adam Witney]

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

Could you select all albums, ORDER BY date DESC LIMIT 1 ?

Adam