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

54
April 06, 2021, at 08:50 AM

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.

READ ALSO
Calculate orientation angle of minutiae point [closed]

Calculate orientation angle of minutiae point [closed]

Want to improve this question? Update the question so it focuses on one problem only by editing this post

41
Add range in PHP scheduling system slots

Add range in PHP scheduling system slots

Helo! I am a beginner in programming and if anyone can help me change a code snippet I would appreciate itI have a scheduling system with time slots, where the slots are generated from variables with the start date until the end date

72
how to send second result in expressjs

how to send second result in expressjs

i want to make Whatsapp bot and integrated to my websitethe system send the qr and users scan it through my website and after users scan and connected i want to send a message to the users it had been connected in the same page

62
Json return [object, Object]

Json return [object, Object]

I'm trying to display json data to html tableThis is my code:

30