Getting the "latest" related record from a SQL DB
bill+london.pm at blunn.org
Fri Oct 10 06:20:57 BST 2014
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
> 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
> 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
> - 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.
More information about the london.pm