From OrganicDesign Wiki
Difficulty with last n created articles query
Lets say we have the following revision table:
id | page
----+----------
1 | Fred
2 | Sam
3 | Bob
4 | Bob
5 | Fred
6 | Harry
7 | Sam
8 | Mary
9 | Mary
10 | Bob
Now if we just wanted the last pages that were edited we'd say:
SELECT DISTINCT page FROM revision ORDER BY id DESC
But we want the last articles created not just edited, so we need a list of the first revision of each article. Here's the same table with the first revision of each article marked
id | page
----+----------
1 | Fred *
2 | Sam *
3 | Bob *
4 | Bob
5 | Fred
6 | Harry *
7 | Sam
8 | Mary *
9 | Mary
10 | Bob
And those need to be listed with a limit and descending order as follows (say limit=3):
id | page
----+----------
8 | Mary
6 | Harry
3 | Bob
What is a query to do that?
I answered my own question - I created test table with those exact items in it and worked out the following query which gives the correct results:
SELECT page,MIN(id) AS minid FROM rev GROUP BY page ORDER BY minid DESC LIMIT 3;
Here's the actual results from the query on my test table:
+-------+-------+
| page | minid |
+-------+-------+
| Mary | 8 |
| Harry | 6 |
| Bob | 3 |
+-------+-------+
