MySQL query problem on group by and max

Question

My table structure is (id,cluster,qid,priority). I'm trying to figure out how I can display the maximum value of priority for each cluster. Say cluster 1 has priorities 100, 102, 105. I want to display the record containing 105. Please help.

Answer 1

Here is an article that explains how to select the row with the max value for each group.

http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

Answer 2

select cluster, MAX(priority) from structure group by cluster;

To find all the columns TRY

select *  from structure
where priority = (
                select MAX(priority) from structure as s 
                where s.cluster = structure.cluster
              );

Answer 3

You could filter out the rows with an inner join, like:

select  s.*
from    structure s
join    (
        select  cluster, MAX(priority) maxprio
        from    structure 
        group by 
                cluster
        ) filter
on      s.cluster = filter.cluster
        and s.priority = filter.maxprio

This would return multiple rows if they all have the maximum priority for that cluster.