+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Select Max records multiple rows (/showthread.php?tid=43920)
Select Max records multiple rows - El Forum - 07-27-2011
[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
Select Max records multiple rows - El Forum - 07-27-2011
[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