Getting the "latest" related record from a SQL DB

[Denny]

On Thu, 2014-10-09 at 13:28 +0100, Andrew Beverley wrote:
> 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?

My first solution to this problem would probably have been to get the
joined rows, then 'order by release_date desc' and grab the first row,
discarding the rest.

Probably gets inefficient if your rows contain big stuff.