0

Let there be a table such as this:

group ID member ID value
0 1 2
0 3 3
0 2 5
1 4 0
1 2 1
2 16 0
2 21 7
2 32 4
2 14 6
3 1 2
... ... ...

The table has three columns, a group ID, which uniquely indentifies a certain group, a member ID, which uniquely identifies members inside individual groups (globally, member ID is not unique), and value, which is something we want to maximize inside each group.

The task is to find a member ID for each group ID which maximizes value. That sounds like a simple enough procedure, right?

Well, apparently, it is not. I'm using MariaDB and have tried everything on this page, such as

select group_id, member_id, value from table x
    join (select max(t.value) as max_v from table t group by group_id) y on y.max_v = x.value;

but none of these approaches yielded the correct result. For completeness, the expected output of the query for the above table would be

group ID member ID value
0 2 5
1 2 1
2 21 7
3 1 2
... ... ...

or even

group ID member ID
0 2
1 2
2 21
3 1
... ...

if returning the value would cause problems. I'd appretiate any sort of help.

Captain Trojan
  • 2,800
  • 1
  • 11
  • 28

1 Answers1

1

This can work, if the `group_id, member_id, value' combination is unique.

SELECT 
  x.group_id, 
  x.member_id,
  x.value
FROM 
  table x
join 
  (SELECT 
    group_id, 
    max(t.value) as max_v 
   FROM table t 
   GROUP BY group_id
  ) y 
ON y.max_v = x.value 
AND y.group_id = y.group_id
Rui Costa
  • 417
  • 2
  • 11