Getting the correct IDs of the maximum values for each group of rows

Question

From a simple table with three rows (id, status, value), I would like to retrieve the id and the value from the row with the maximum value, for each status group.

The simple query I tried is:

SELECT t1.status, t1.id, MAX(t1.value) FROM t1
GROUP BY t1.status;

The problem is that I do get maximum value for each group, but not the correct id. I get the the id of the first row from each status group... Is there a simple way to get the proper id?

There are many questions about getting the maximum value, but aren't many about the id. Couldn't find an answer for that one..

Use an 'ORDER BY desc' on t1.value, and then take the first row (in addition to your GROUP BY) — pellucidcoder, Oct 22 '17 at 17:14
Sounded like a promising solution. I tried it with a subquery instead of sthe simple t1, but it still gives me the same answer. would you preform the ORDER by in a different way? — Elon, Oct 22 '17 at 17:48

score 0 · Answer 1 · answered Oct 22 '17 at 17:14

0

You need to group by t1.status and t1.id use below query-

SELECT t1.status, t1.id, MAX(t1.value) FROM t1
GROUP BY t1.status,t1.id;

answered Oct 22 '17 at 17:14

Lokesh Kumar Gaurav

726
1
8
24

could you explain the logic? it doesn't work. – Elon Oct 22 '17 at 17:44
Since you are selecting id so you should group by id else id will come wrong for the max value. – Lokesh Kumar Gaurav Oct 22 '17 at 17:48

nacho · Answer 2 · 2017-10-22T17:27:47.367

0

Try with a subquery to get the id that has the max value:

   SELECT a.status, (select b.id
                       FROM t1 as b
                       where b.status=a.status
                       group by b.status
                       HAVING b.value=max(b.value)), 
           MAX(a.value) 
    FROM t1 as a
    GROUP BY a.status;

edited Oct 22 '17 at 17:27

answered Oct 22 '17 at 17:21

nacho

5,280
2
25
34

pellucidcoder · Answer 3 · 2017-10-23T19:25:10.023

0

I have a better idea than what I put in the comment. Not the most efficient but should get the job done:

WITH maxes(max) as (
    SELECT MAX(t1.value), t1.status
    FROM t1
    GROUP BY t1.status),
SELECT t1.status, t1.id, t1.value
   FROM t1, maxes
   WHERE t1.value = maxes.max AND t1.status = maxes.status;

edited Oct 23 '17 at 19:25

answered Oct 22 '17 at 17:58

pellucidcoder

127
3
9

Sorry, but what is m.max? – Elon Oct 23 '17 at 15:20
Ah should be `maxes.max`. I will update. – pellucidcoder Oct 23 '17 at 19:24

score 0 · Answer 4 · answered Oct 23 '17 at 16:10

0

This solution worked for me:

SELECT v.status, v.id, v.value
 FROM 
(SELECT statues, MAX(value) AS maxVal
FROM t1
GROUP BY status) as m
INNER JOIN t1 v
ON v.status = m.status 
AND v.value = m.maxVal;

Thank you!

answered Oct 23 '17 at 16:10

Elon

37
8

Getting the correct IDs of the maximum values for each group of rows

4 Answers4