Getting the "latest" related record from a SQL DB

[Abigail]

On Sun, Oct 12, 2014 at 05:00:10PM +0100, Andrew Beverley wrote:
> 
> 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.

Ah, the perils of not asking what you want.

>          Therefore (I think) I can't use either of those options without
> doing several database queries.

You can using a subselect:

  SELECT  Artist.*, Album1.name      
    FROM  Artist, Album Album1
   WHERE  Artist.id = Album1.artist_id
     AND  Artist.id IN (... list of artist IDs ...)
     AND  Album1.release_date = (SELECT  MAX(Album2.release_date)
                                   FROM  Album Album2        
                                  WHERE  Album2.artist_id = Album1.artist_id);

This time, I actually tested the query.

>                                 I also like to avoid sub-selects as
> MySQL has a tendency to optimise them badly (as Gareth said).

It's much better to measure the performance of the various queries on
your actual data than tossing some out of the window due to "tendencies".

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


Then you shouldn't start with discarding queries because of possible
performance issues at a specific database.



Abigail