Getting the "latest" related record from a SQL DB

[David Cantrell]

On Thu, Oct 09, 2014 at 01:28:44PM +0100, Andrew Beverley wrote:

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

Albums can have more than one artist, of course ...

> 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'm sure it must be possible with a HAVING and GROUP BY, but I'm
buggered  if I can get it to work.

-- 
David Cantrell