MySql Group by
Hello everyone, so I have the following table, 't1'
id r_id o_id count
1 2 100 1
2 3 100 1
3 5 100 1
4 2 101 2
5 3 101 2
6 4 101 2
What I am trying to do is, for a given list of r_id, return r_id and o_id, where count is the highest (max). So ideally, given the r_id of 3 and 5, I would get this:
r_id o_id
5 100
3 101
I've tried the following:
select o_id, max(count), r_id from t1 where r_id IN (3, 5) group by r_id;
but it doesn't seem to give me the o_id related right. Any ideas?
As you say you want r_id
and o_id
how two output columns ...:
SELECT r_id, o_id
FROM t1 AS a
WHERE r_id in (3, 5)
AND count =
(SELECT MAX(count)
FROM t1 AS b
WHERE b.r_id = a.r_id
)
If for some r_id
there is more than one o_id
with the same "maximum score", this will return them all (instead of picking one of them arbitrarily).
In MySQL 5.1 with your example table:
mysql> select * from t1;
+------+------+------+-------+
| id | r_id | o_id | count |
+------+------+------+-------+
| 1 | 2 | 100 | 1 |
| 2 | 3 | 100 | 1 |
| 3 | 5 | 100 | 1 |
| 4 | 2 | 101 | 2 |
| 5 | 3 | 101 | 2 |
| 6 | 4 | 101 | 2 |
+------+------+------+-------+
6 rows in set (0.00 sec)
this query gives me exactly your requested result:
+------+------+
| r_id | o_id |
+------+------+
| 5 | 100 |
| 3 | 101 |
+------+------+
2 rows in set (0.31 sec)
By the way, my exact version is:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.1.34 |
+-----------+
1 row in set (0.00 sec)
a source to share