Select Max records multiple rows

[eluser]dazed[/eluser]
Hi Guys,

I know the title may not be the best description.

I have a DB with a revision system. I need to collect all articles with the highest revision number.

Code:

+------------+------------+----------------+
| articleid  | revisionid | revisionnumber |
+------------+------------+----------------+
|          1 |          1 |              1 |
|          2 |          2 |              1 |
|          3 |          1 |              2 |
|          4 |          1 |              3 |
|          5 |          1 |              4 |
|          6 |          2 |              2 |
|          7 |          2 |              3 |
|          8 |          1 |              5 |
|         12 |          1 |              6 |
|         13 |          1 |              7 |
+------------+------------+----------------+

so the result should look

Code:

+------------+------------+----------------+
| articleid  | revisionid | revisionnumber |
+------------+------------+----------------+
|          7 |          2 |              3 |
|         13 |          1 |              7 |
+------------+------------+----------------+

closest i got was

Code:

$query = $this->db->query('select articleid, revisionid, max(revisionnumber) from articles where userid = 83 group by revisionid');

I have googled loads but cannot get more than one result back.

Cheers

Dazed

[eluser]jmadsen[/eluser]

Code:

SELECT articleid, revisionid, revisionnumber
FROM articles
WHERE revisionnumber = (
SELECT max( revisionnumber )
FROM articles a2
WHERE a2.revisionid = articles.revisionid )
GROUP BY revisionid