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