Through this stack overflow question we can get the rows with maximum value as
select yt1.*
from yourtable yt1
left outer join yourtable yt2
on (yt1.id = yt2.id and yt1.rev < yt2.rev)
where yt2.id is null;
and assume the above result gives
+------+-------+--------------------------------------+
| id | rev | content |
+------+-------+--------------------------------------+
| 1 | 3 | 15 |
| 2 | 1 | 18 |
| 4 | 2 | 14 |
+------+-------+--------------------------------------+
I am joining another table which has the above id as primary key
+------+-------+-------------+
| id | myId | type |
+------+-------+-------------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 1 |
| 4 | 2 | 1 |
+------+-------+-------------+
as
select bt.myId, yt1.*
from baseTable bt
inner join yourtable yt1 (on bt.id=yt1.id)
left outer join yourtable yt2
on (yt1.id = yt2.id and yt1.rev < yt2.rev)
where yt2.id is null;
and the join gives the result as
+------+------+-------+-----------------------+
| myId | id | rev | content |
+------+------+-------+-----------------------+
| 1 | 1 | 3 | 15 |
| 1 | 2 | 1 | 18 |
| 2 | 4 | 2 | 14 |
+------+------+-------+-----------------------+
Now how can I get the rows with max of content value based on myId. ie, the final output will be the sets [1,2,1,18] and [2,4,2,14]. It can be achieved by creating view of first result and again doing the same type of query for the view too. Is there any methods without view???
