[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