Extension talk:RecentActivity

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:

<mysql> SELECT DISTINCT page FROM revision ORDER BY id DESC </mysql>

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:

<mysql> SELECT page,min(id) AS minid FROM rev GROUP BY page ORDER BY minid DESC LIMIT 3; </mysql>

Here's the actual results from the query on my test table:

+-------+-------+
| page  | minid |
+-------+-------+
| Mary  |     8 | 
| Harry |     6 | 
| Bob   |     3 | 
+-------+-------+