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.
