How to find rows with max priority in MySql?

Question

[ Sorry if this is a repost ]

I have several products. They can belong to multiple groups, i.e. one product may belong to more than 1 group. Each group has a priority. I have 2 tables one which has product_id-group_id mapping, and another which has group_id-priority mapping. I need a product_id-group_id result which returns group for a product_id which has max priority.

e.g. Source Tables :

table1 = product_id | group_id      table2 = group_id | priority
         1          | g1                     g1       | 1   
         1          | g2                     g2       | 2
         2          | g1                     g3       | 3
         2          | g2
         2          | g3

Desired Result :

product_id | group
1          | g2        # because out of groups g1 and g2, g2 has more priority
2          | g3        # 3 = max_priority among groups g1, g2 and g3

I need a SQL query which will return this result. It would be better if you guys can come up with a query such that it returns the mapping for only a list of desired product_ids.

We are using MySQL 5.5. Unique product_ids will be < 1m, groups will not be more than 1000.

Any help or pointers appreciated. Thanks all.

Answer 1

You can do this using substring_index() and group_concat():

select t1.product_id,
       substring_index(group_concat(t1.group_id order by t2.priority desc), ',', 1) as group_id
from table1 t1 join
     table2 t2
     on t1.group_id = t2.group_id
group by t1.product_id;