Take the 2-minute tour ×

I am querying a table named artists, but I would like to sort the response based on a table named painings (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 (painings have a column named date_created). Does anyone know how this could be done?

share|improve this question
add comment

5 Answers

up vote 2 down vote accepted

Ideally this should be done using ANSI joins:

FROM            artists a
     INNER JOIN paintings p
     ON         a.artistID = p.artistID
ORDER BY        p.date_created desc
share|improve this answer
add comment

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
share|improve this answer
add comment

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
share|improve this answer
add comment

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.

share|improve this answer
add comment

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

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.

share|improve this answer
I think you need to add the DESC to order by clause, as the requirement is to show the most recent first.. but its almost trivial. –  Manoj Mar 31 '11 at 15:18
Hah I was fixing that as you mentioned it. Thanks though! –  Dan Fego Mar 31 '11 at 15:22
add comment

Your Answer


