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

Question

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 of 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

Answer 1

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.

Answer 2

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 =)

Answer 3

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`)