SQL : Using GROUP BY and MAX on multiple columns

Question

I have an issue with an SQL Query. Lets take this example data

itemID  catID  attrib1  attrib2
  1       1       10       5   
  2       1       10       7
  3       1        5      10
  4       2       18      15

I want to return the best item for each category (with attrib1 having priority over attrib2)

Obviously, SELECT catID, MAX(attrib1), MAX(attrib2) FROM test_table GROUP BY catID doesn't work since it will return 10 & 10 for the 1st cat.

So is there anyway to tell MySQL to select max value from attrib2 row but only consider the ones where attrib1 is also max value ? i.e return the following data

 catID  attrib1  attrib2
   1       10       7   
   2       18      15

score 12 · Accepted Answer · answered Oct 28 '10 at 17:56

12

You can get the best attrib1 values, and then join in the attrib2 values and get the best of those for each attrib1 value:

select t2.catID, t2.attrib1, max(t2.attrib2)
from
(
  select catID, max(attrib1) as attrib1
  from test_table
  group by catID
) t1
inner join test_table t2 on t2.catID = t1.catID and t2.attrib1 = t1.attrib1
group by t2.catID, t2.attrib1

answered Oct 28 '10 at 17:56

Guffa

687,336
108
737
1,005

Thanks for all the quick answers guys. It's nice to also see different ways of doing it. – Charles Oct 28 '10 at 19:44
Does this work? the 3 returning values should be the ones of different rows. – Ryo Dec 30 '16 at 07:10
should avoid this as join will put a lot of load – Shahrukh Khan Jan 19 '20 at 20:27

score 2 · Answer 2 · answered Oct 28 '10 at 17:50

Use:

SELECT x.catid,
       x.max_attrib1 AS attrib1,
       (SELECT MAX(attrib2)
          FROM YOUR_TABLE y
         WHERE y.catid = x.catid
           AND y.attrib1 = x.max_attrib1) AS attrib2
  FROM (SELECT t.catid,
               MAX(t.attrib1) AS max_attrib1
          FROM YOUR_TABLE t
      GROUP BY t.catid) x

score 1 · Answer 3 · answered Oct 28 '10 at 17:44

1

SELECT tt.catId, tt.attrib1, MAX(tt.attrib2)
FROM   test_table tt
GROUP BY tt.catID, tt.attrib1
WHERE  tt.attrib1 = (SELECT MAX(t2.attrib1) FROM test_table t2 WHERE t2.catID = tt.catID)

answered Oct 28 '10 at 17:44

Andrew

14,325
4
43
64

score -2 · Answer 4 · answered Oct 28 '10 at 17:45

-2

SELECT catID, max1, max2 FROM
((SELECT Max(attrib1) as max1, catID GROUP BY attrib1) as t1
INNER JOIN
(SELECT MAX(attrib2) as max2, catID GROUP BY attrib2) as t2
ON t1.catID = t2.catID) as t3

answered Oct 28 '10 at 17:45

Martijn

11,964
12
50
96

SQL : Using GROUP BY and MAX on multiple columns

4 Answers4

Linked

Related