Select maximum value of each member from table

Question

I want to select the best result of each member from the mysql table, for a given discipline. (if there are entries with the same value, the entries with the lowest event start date should be taken)

DDLs:

 CREATE TABLE `results` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `discipline` int(11) NOT NULL,
      `member` int(11) DEFAULT '0',
      `event` int(11) DEFAULT '0',
      `value` int(11) DEFAULT '0',
      PRIMARY KEY (`id`),
      UNIQUE KEY `member_2` (`member`,`discipline`,`event`)
     );

     INSERT INTO results VALUES
     (1,1,2,4,10),
     (2,1,1,4, 8),
     (3,1,2,5, 9),
     (4,2,3,5, 9),
     (5,1,2,6,11),
     (6,1,2,7,11),
     (7,1,2,1,11),
     (8,1,2,3, 7);

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `startDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO events VALUES
(1 ,'Not in scope','2012-05-23'),
(3 ,'Test 0', '2014-05-09'),
(4 ,'Test 1', '2014-05-10'),
(5 ,'Test 2', '2014-05-11'),
(6 ,'Test 3', '2014-05-12'),
(7 ,'Test 4', '2014-05-13');

SELECT * FROM results;
+----+------------+--------+-------+-------+
| id | discipline | member | event | value |
+----+------------+--------+-------+-------+
|  1 |          1 |      2 |     4 |    10 |
|  2 |          1 |      1 |     4 |     8 |
|  3 |          1 |      2 |     5 |     9 |
|  4 |          2 |      3 |     5 |     9 |
|  5 |          1 |      2 |     6 |    11 |
|  6 |          1 |      2 |     7 |    11 |
|  7 |          1 |      2 |     1 |    11 |
|  8 |          1 |      2 |     3 |     7 |
+----+------------+--------+-------+-------+

SELECT * FROM events;
+----+--------------+---------------------+
| id | name         | startDate           |
+----+--------------+---------------------+
|  1 | Not in scope | 2012-05-23 00:00:00 |
|  3 | Test 0       | 2014-05-09 00:00:00 |
|  4 | Test 1       | 2014-05-10 00:00:00 |
|  5 | Test 2       | 2014-05-11 00:00:00 |
|  6 | Test 3       | 2014-05-12 00:00:00 |
|  7 | Test 4       | 2014-05-13 00:00:00 |
+----+--------------+---------------------+

Result should be:

+---------+------------+--------+-------+-------+
| id      | discipline | member | event | value |
+---------+------------+--------+-------+-------+
|       3 |          1 |      1 |     4 |     8 |
|       5 |          1 |      2 |     6 |    11 |
+---------+------------+--------+-------+-------+

My first approach was to group by member id, but it's not that easy. So I tried a lot of different approaches from the web and from my colleages.

The last one was:

select res.* 
from `results` as res
join (select id, max(value) 
      from results
      join events on results.event = events.id 
      where discipline = 1
      events.name like 'Test%' 
      Group by id
      Order by events.startDate ASC) as tmpRes 
on res.id = tmpRes.id
group by member
order by value DESC

But the result in this example would be a random result id for member 2.

If a member got the same score twice, do you care which id is selected? What if the scores (which are the same) are in different disciplines? — Patrick Q, May 12 '14 at 19:15
I just noticed the `where discipline = 1` in your example query, which makes my second question moot (and also answers @LaughDonor's question). — Patrick Q, May 12 '14 at 19:25
@Strawberry I add the DDLs from the two tables, the result set is only the two entries (result id: 3 and 5) — knobli, May 13 '14 at 09:30

Strawberry · Accepted Answer · 2014-05-13T22:23:20.953

1

Should be correct now, but let me know if there's a mistake...

 SELECT r.*
   FROM events e
   JOIN results r
     ON r.event = e.id
   JOIN
      ( SELECT r.member
             , MIN(e.startdate) min_startdate      
          FROM events e
          JOIN results r
            ON r.event = e.id
          JOIN 
             ( SELECT member
                    , MAX(value) max_value 
                    , discipline
                 FROM events e 
                 JOIN results r 
                   ON r.event = e.id 
                WHERE discipline = 1 
                  AND name LIKE 'Test%' 
                GROUP 
                   BY member
             ) x
            ON x.member = r.member
           AND x.max_value = r.value
           AND x.discipline = r.discipline
           AND e.name LIKE 'Test%'
         GROUP
            BY member
      ) y
     ON y.member = r.member
    AND y.min_startdate = e.startdate;

Although fast, because these queries can get rather complex and cumbersome, there's an undocumented hack that achieves the same result. It goes something like this...

 SELECT * 
   FROM 
      ( SELECT r.* 
          FROM events e 
          JOIN results r  
            ON r.event = e.id 
         WHERE discipline = 1 
           AND name LIKE 'Test%' 
         ORDER 
            BY member
             , value DESC
             , startdate
      ) x 
  GROUP 
     BY member;

edited May 13 '14 at 22:23

answered May 13 '14 at 12:16

Strawberry

33,750
13
40
57

Is there no way to include the event date? The query works, but I found some entries that were not the oldest. The result id is not correlating to the date – knobli May 13 '14 at 16:29
There was an error before - *should* be OK now – Strawberry May 13 '14 at 17:33
you forgot the "AND discipline = 1" at the end of the furthest selection. There are multiple discipline in one event. Thank you very mush – knobli May 13 '14 at 20:23
Are you sure it's necessary? I don't think so!?!?! – Strawberry May 13 '14 at 22:25
I tested it, you lose the relation to the value or discipline in the furthest selection. I got results from all disciplines and not only from discipline with id 1 – knobli May 13 '14 at 23:01
That points to a flaw in your design! But I guess you have to work with what you've got! – Strawberry May 13 '14 at 23:05

Dave Jemison · Answer 2 · 2014-05-12T22:36:52.977

0

If I understand your question correctly, you need to group on member in the sub-query. Try the following:

select res.* 
from `results` as res
join (select member, min(event) AS minEvent, max(value) AS maxValue
  from results 
  where discipline = 1 
  Group by member) as tmpRes 
  on res.member = tmpRes.member AND res.event=tmpRes.minEvent AND res.value=tmpRes.maxValue
order by res.value

EDIT (bast on most recent comment): If that's the case, you'll need to join on the Events table. Unless the startDate field is actually a temporal field, it's going to be a big mess.

It would have made things easier with all the requirements included in the original question.

edited May 12 '14 at 22:36

answered May 12 '14 at 19:19

Dave Jemison

694
3
7

I'm sorry, I forgot to tell, there is also an column event. I tought there is no impact about that, but as I test your approach, I found out there is a difference. I updated my questions – knobli May 12 '14 at 21:12
Please update your question to show the data and expected results and I can update my answer. – Dave Jemison May 12 '14 at 21:16
You were too quick for me :) – knobli May 12 '14 at 21:20
My answer should still give you the desired results. – Dave Jemison May 12 '14 at 21:29
Sorry missed to add one line to the table. (if there are entries with the same value, the entries with the lowest event id should be taken) – knobli May 12 '14 at 21:41
I updated my answer to reflect. – Dave Jemison May 12 '14 at 21:52
Thanks, that works. I tried to add the relation to the event table and select not the smallest event id but the event with the oldest start date and not every event only events with an specific name pattern (question updated) – knobli May 12 '14 at 22:22
Awfully lucky that the min event has the max value ?!?!?! – Strawberry May 12 '14 at 22:36
@DaveJemison I updated the original question with all the information. Sorry I thought the first post covered all my questions, but than I recognized step by step that's there are more and more challenges – knobli May 13 '14 at 06:08
@DaveJemison Yes, it would have. But, in any event, this answer is clearly flawed. – Strawberry May 13 '14 at 09:02

Select maximum value of each member from table

2 Answers2