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

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.

Solution

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