Getting the "latest" related record from a SQL DB

[Andrew Beverley]

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

Thanks for all the replies. Two good options from several people: limit
the result to one row, or use a sub-select.

What I failed to say was that as well as doing this for a single artist
as per my question, I'd like to also be able to do it for several
artists. Therefore (I think) I can't use either of those options without
doing several database queries. I also like to avoid sub-selects as
MySQL has a tendency to optimise them badly (as Gareth said).

What I also failed to say was that I'm using DBIC and want to keep
things database agnostic.

So, I used Bill's suggestion, which seems to work well
(http://london.pm.org/pipermail/london.pm/Week-of-Mon-20141006/025481.html)

To get it to work with DBIC I had to create a custom join condition.
Something like:

$class->might_have(
    album_later => 'Album',
    sub {
        my $args = shift;

        return {
            "$args->{foreign_alias}.artist_id"  => {
                -ident => "$args->{self_alias}.artist_id"
            },
            "$args->{foreign_alias}.released" => {
                '>' => \"$args->{self_alias}.released"
            },
        };
    }
);

And then add the relevant search criteria when using it:

    $search = {
        {'album' => 'album_later'},
        ...
    }

Thanks again for all the replies.

Andy