Getting the "latest" related record from a SQL DB

[Smylers]

Andrew Beverley writes:

> But what if I want to fetch an artist's details and his latest album?

A reasonable thing to want to do.

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

Faffy, but would work.

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

Faffy and error-prone, because you're storing information twice, and
risks getting out of sync.

> Neither seem particularly tidy to me, so am I missing something
> completely obvious?

I'd go with the approach you dismissed as “isn't possible”. Let us know
which DBMS you're using and we can show you how. (The syntax differs
between, say, Postgres and MySQL.)

Cheers

Smylers
-- 
http://twitter.com/Smylers2