CodeIgniter Forums
Select Max records multiple rows - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Select Max records multiple rows (/thread-43920.html)



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