sort sql query by values in another table

I am querying a table named artists, but I would like开发者_开发问答 to sort the response based on a table named paintings (an artist has_many paintings - the painting table has an artist_id column).

To be more specific, I want to sort the artists by their most recent painting (paintings have a column named date_created). Does anyone know how this could be done?

Ideally this should be done using ANSI joins:

SELECT DISTINCT a.artist
FROM            artists a
     INNER JOIN paintings p
     ON         a.artistID = p.artistID
ORDER BY        p.date_created desc

Perhaps something like this, depending on the specifics of your schema?

SELECT DISTINCT artists.* FROM
artists, paintings
WHERE artists.id = paintings.artist_id
ORDER BY paintings.painting_date DESC;

This will join the two tables on the artist id, and then order by their painting dates. DISTINCT ensures you only get one row per artist.

This will only return each artist once, with the latest date_created value for that artist.

SELECT artists.name, paintings.date_created
FROM artists JOIN (
    SELECT artist_id, MAX(date_created) as date_created FROM paintings GROUP BY artist_id
) paintings ON artists.id = paintings.artist_id
ORDER BY paintings.date_created DESC

If I understand your requirement correctly:

1) Write an aggregation query that returns each artist and his/her latest painting; 2) Use it as a sub-query, joining it to the artists table; 3) SELECT columns from the join, ordering by the date of latest painting.

You can create a query:

         select artistid, max(paintingdate)
         from paintings
         group by artistid

and then join to that as an inline-view:

       select artistname from artist
       inner join
       ( 
         select artistid, max(paintingdate) as latestdate
         from paintings
         group by artistid
        ) as Foo
        on artist.artistid = Foo.artistid
        order by latestdate desc