A mysql query that gives the most recent revision for each entity

I have my data split across two tables. One is the entity table where it describes the basics of the entity, and the revision table where the data o开发者_运维问答f an entity resides. If I know the entity I can query for the most recent version:

SELECT *
FROM `entities`
  JOIN (SELECT *
        FROM `revisions`
        WHERE `entity_fk` = ?
        ORDER BY `revised` DESC
        LIMIT 0, 1) as `revision`
    ON `entities`.`id` = `revision`.`entity_fk`

But now I need to list all entities using data from their most recent revision.

revisions table
+--+---------+---------+--------------------+-------------------+
|id|entity_fk|title    |body                |revised            |
+==+=========+=========+====================+===================+
|01|    01   |Hentity A|This Is tH3 first...|2010-08-30 10:02:45|
|02|    01   |Entity A |This is the first...|2010-08-30 10:16:30|
|03|    02   |Entity B |This is another...  |2010-08-30 10:20:20|

What the query should return is a list of the most recent revisions for each entity, not just a specified entity.

2,1,Entity A,This is the first...,2010-08-30 10:16:30
3,2,Entity B,This is another...,2010-08-30 10:20:20

---

I suggest sticking with something simple:

select *
from `entities` e join `revisions` r1
  on e.`id` = r1.`entity_fk`
where r1.`revised` = ( 
   select max(r2.`revised`)
   from `revisions` r2
   where r1.`entity_fk` = r2.`entity_fk`
)

If mysql doesn't support the scalar subquery, I suggest using a self-join on revisions with group by and having clauses. However, the group by will be long because you will have to include all the columns from e and r1.

e.g.

select e.*, r1.*
from `entities` e join `revisions` r1
  on e.`id` = r1.`entity_fk`
join `revisions` r2
  on r1.`entity_fk` = r2.`entity_fk`
group by ...
having r1.`revised` = max(r2.`revised`)

---

If I'm reading your question right this would do:

select
    *
from
    `entities` as e,
    `revisions` as r
where
    r.entity_fk=e.id
group by
    e.id
order by
    r.revised desc

You get the result set ordered by revisions.revised though.

---

i didn't completely understood what you are trying to do... but i think that you want to do is to list all the entities including the date of their last modification.. if that's it, you could use a GROUP BY, and a MAX() function like this

SELECT e.title,e.body,MAX(r.revised) FROM `entities` e JOIN `revisions` r ON r.entity_fk = e.id GROUP BY e.title,e.body

BTW.. i'd change the name of "entity_fk" to "entity_id", its kinda confusing.. at least for me =)