SQL query for nth most recent update

Question

I have a revision table and I need to be able the nth most recent update. I have a query that gives me the most recent revision record of enitity, but I need one for the nth most recent revision record.

revisions
--+---------+--------+----------+-------
id|entity_id|contents|revisor_fk|revised

The query must take input 0 to n. If the input is 0 it is the most recent, 1 is one revision back or second most recent, 2 is to revisions back or the 3rd most recent, etc. And if the input is more revisions back then the entity has revisions it should return no rows.

Any thoughts?

score 2 · Accepted Answer · answered Aug 27 '10 at 21:04

2

Assuming revised is the timestamp

SELECT fields
FROM revisions
WHERE entity_id = :your_entity_id
ORDER BY revised DESC
LIMIT :which_revision, 1

answered Aug 27 '10 at 21:04

Matti Virkkunen

63,558
9
127
159

+1 This is the most simplistic query I have seen for this problem. Very tight! – Tyson of the Northwest Aug 27 '10 at 21:09
`LIMIT` is supported by MySQL, Postgres and SQLite. For approaches for other DBMSs, see http://en.wikipedia.org/wiki/Select_%28SQL%29#Limiting_result_rows – bobince Aug 27 '10 at 21:37

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

Ran across this at 2937755

SELECT *
  FROM revisions a
 WHERE (4) = (select count(*)
                from revisions b
               where b.`revised` > a.`revised`)AND
`entity_id` = 1

But @Matti Virkkunen's solution is much nicer.

edited May 23 '17 at 12:18

Community

1
1

answered Aug 27 '10 at 21:11

Tyson of the Northwest

2,086
2
21
34

SQL query for nth most recent update

2 Answers2