How to select records based on the max value of two fields?

Question

Given the following simple table:

+-----+-------------+---------+----+
| id_ | match_op_id | version | p1 |
+-----+-------------+---------+----+
|   1 |           1 |       1 |  1 |
|   2 |           1 |       1 |  5 |
|   3 |           1 |       2 |  3 |
|   4 |           1 |       2 |  4 |
|   5 |           2 |       1 |  1 |
|   6 |           2 |       1 |  5 |
|   7 |           2 |       2 |  3 |
|   8 |           2 |       2 |  4 |
|   9 |           2 |       2 |  4 |
+-----+-------------+---------+----+

I want to build a query that selects the match_op_id and p1 fields for a single record (doesn't matter which one) for each match_op_id from the max version and then the max p1. So from the above I would get the output:

+-------------+----+
| match_op_id | p1 |
+-------------+----+
|           1 |  4 |
|           2 |  4 |
+-------------+----+

Following some posts on SO I've built a query that selects all records where the p1 field is the maximum value:

SELECT 
    odds_op.match_op_id, odds_op.p1
FROM
    odds_op,
    (SELECT 
        match_op_id, MAX(p1) AS p1
    FROM
        odds_op
    GROUP BY match_op_id) AS max_p1
WHERE
    odds_op.match_op_id = max_p1.match_op_id
        AND odds_op.p1 = max_p1.p1

I now can't figure out how to ensure I only select the maximum p1 from the maximum version. I think it's probably a nested sub query but I can't figure it out. I also know I'll run into some issues with grouping so that I don't end up with multiple records per match_op_id. Any help would be much appreciated.

update your question add a proper data sample and the expcted result — ScaisEdge, Apr 04 '21 at 16:44
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Apr 04 '21 at 18:50
Are you looking for "groupwise-max"? Or `ORDER BY .. LIMIT 1`? — Rick James, Apr 10 '21 at 23:43

forpas · Accepted Answer · 2021-04-10T16:30:40.407

For MySql 8.0+ you can do it with FIRST_VALUE() window function:

SELECT DISTINCT match_op_id,
       FIRST_VALUE(p1) OVER (PARTITION BY match_op_id ORDER BY version DESC, p1 DESC) p1
FROM odds_op

For previous versions, use NOT EXISTS to filter the table, so that only rows with the max version of each match_op_id are returned and then aggregate to get the max p1:

SELECT o1.match_op_id, MAX(o1.p1) p1
FROM odds_op o1
WHERE NOT EXISTS (
  SELECT 1 
  FROM odds_op o2 
  WHERE o2.match_op_id = o1.match_op_id AND o2.version > o1.version
)
GROUP BY o1.match_op_id

Or with a correlated subquery in the WHERE clause:

SELECT o1.match_op_id, MAX(o1.p1) p1
FROM odds_op o1
WHERE o1.version = (SELECT MAX(o2.version) FROM odds_op o2 WHERE o2.match_op_id = o1.match_op_id)
GROUP BY o1.match_op_id

See the demo.
Results:

match_op_id p1

1 4

2 4

Lukasz Szozda · Answer 2 · 2021-04-11T08:33:08.047

You could use GROUP_CONCAT aggregation function that allows for sorting and take first element of created string:

SELECT match_op_id,  
SUBSTRING_INDEX(GROUP_CONCAT(p1 ORDER BY version DESC, p1 DESC SEPARATOR '~'),'~',1) AS p1
FROM odds_op
GROUP BY match_op_id;

Output(added GROUP_CONCAT as separate debug column):

+--------------+-----+-----------+
| match_op_id  | p1  |   debug   |
+--------------+-----+-----------+
|           1  |  4  | 4~3~5~1   |
|           2  |  4  | 4~4~3~5~1 |
+--------------+-----+-----------+

db<>fiddle demo

If you are using MariaDB this is even simpler using LIMIT clause.

SELECT match_op_id, 
       GROUP_CONCAT(p1 ORDER BY version DESC, p1 DESC LIMIT 1) AS p1
FROM odds_op
GROUP BY match_op_id;

db<>fiddle demo MariaDB

score 0 · Answer 3 · answered Apr 13 '21 at 10:11

I'm not complete sure about you desire result.

You said: "I now can't figure out how to ensure I only select the maximum p1 from the maximum version."

This is a way:

SELECT MAX(p1)
FROM odds_op
WHERE version = (SELECT MAX(version) FROM odds_op);

If you want select match_op_id and p1 pairs where version is max version, and then p1 is max p1 (max p1 where version is max version), then check this query:

SELECT DISTINCT match_op_id, p1
FROM odds_op
WHERE version = (SELECT MAX(version) FROM odds_op)
    AND p1 = (SELECT MAX(p1)
                FROM odds_op
                WHERE version = (SELECT MAX(version) FROM odds_op));

Try different responds with this values and check the results:

INSERT INTO odds_op (id_, match_op_id, version, p1) VALUES
  ('1', '1', '1', '1'),
  ('2', '1', '1', '5'),
  ('3', '1', '2', '3'),
  ('4', '1', '2', '4'),
  ('5', '2', '1', '1'),
  ('6', '2', '1', '5');

For my example data, my query return (It doesn't return pair: 2,5):

match_op_id	p1
1	4

For your example data the result is the same you want.

score 0 · Answer 4 · answered Apr 15 '21 at 08:06

Its a bit cheaty, and you would have to be certain you know the upper bound of your p1 values:

SELECT match_op_id, MAX(10*version+p1)-MAX(10*version) AS p1
FROM odds_op
GROUP BY match_op_id

This would work for p1s below up to 9. You need to add a zero to both 10s in the query for every digit in your greatest p1

score 0 · Answer 5 · answered Apr 16 '21 at 03:55

This script is working on mysql. Hope this works on you too. Happy Coding!

SELECT o1.match_op_id, MAX(o1.p1) AS p1
FROM odds_op o1
INNER JOIN (
              SELECT match_op_id, MAX(version) AS version
              FROM odds_op
              GROUP BY match_op_id
           ) AS o2 ON o1.match_op_id = o2.match_op_id AND o1.version = o2.version
GROUP BY o1.match_op_id

How to select records based on the max value of two fields?

5 Answers5