MySql Group by

Question

Hi all 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'm trying to do is, for a given list of r_id's, return the r_id and o_id where the count is the highest (max). So ideally, given r_id's of 3 and 5, I would get the following:

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 right associated o_id. Any ideas?

Answer 1

Harder than i thought at first glance - this should work:

select a.r_id,a.o_id from t1 a inner join (select r_id,max(count) as max from t1 group by r_id) as b on a.r_id = b.r_id and a.count = b.max where a.r_id in (3,5)

Answer 2

As you say you want r_id and o_id as the 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 there's more than one o_id with the same "maximal count" for a certain r_id, this will return them all (rather than picking one of them arbitrarily).

On MySQL 5.1 with your sample 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)

BTW, my exact version:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.1.34    | 
+-----------+
1 row in set (0.00 sec)