Getting the "latest" related record from a SQL DB

[William Blunn]

On 09/10/2014 13:28, Andrew Beverley wrote:
> 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.

You wrote "an artist's ...", meaning you want the answer for one artist.

Assuming you have no case where an artist has two or more albums with 
distinct release dates, then you can do a simple (inner) join on both 
tables, order in the relevant direction by release date, and take the 
first/last result.

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

Unnecessary once you have realised that you don't need an aggregate 
function (MAX).

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

Probably unnecessary. If you have a mega-scale application, which is 
heavy on reads, then it is possible that pre-computing such answers 
might be economical. But that would be an optimisation you would 
probably want to do later.

Regards,

Bill