MySQL query: select max(A) and use MAX(pkey) if multiple max(A) records exist

Question

I've been trying to find a solution for this problem using just 1 join. Is it possible?

table:

pkey | uid | A | B | C
100 | 1 | 5 | 2 | 
101 | 1 | 5 | 3 | 'should find this'
102 | 1 | 4 | 6 | 
103 | 2 | 5 | 2 | 'should find this'
104 | 3 | 2 | 7 | 
105 | 3 | 1 | 1 | 
106 | 3 | 2 | 1 | 'should find this'

Now i need to select records with pkey 101 and 103. I use this query to select max(B).

SELECT table.*
FROM (
 SELECT uid, max(A) as maxA
 FROM table
 GROUP BY uid
) as maxlog
JOIN table 
ON table.uid = maxlog.uid 
AND table.A = maxlog.maxA

It returns two records for uid 1 (pkey 100 and 101). How can i filter on max(B) in the same query with adding another JOIN?

• Rick James: Another approach: mysql.rjweb.org/doc.php/groupwise_max#using_variables
• RDFozz: If we added two more rows to table A: (104,2,5,7,''), (105,2,5,1,''). Now, which rows should be returned: pkeys 101 and 105, or pkeys 101 and 104?
• user2252031: i added some more records for clarification

Answer 1

It looks like what you want is: for each uid, find the MAX(pkey) associated with the MAX(A) value of A.

If that's what you need, try this:

SELECT table.*
FROM (
 SELECT uid, A, MAX(pkey) as pkey
 FROM table t
 WHERE A = (SELECT MAX(A) FROM table WHERE uid = t.uid)
 GROUP BY uid, A
) as maxlog
JOIN table 
ON maxlog.pkey = table.pkey

Note that, while this technically only has one JOIN, that's really just semantics - the SELECT MAX(A) subquery is, in effect, a JOIN (and might perform better if written as such).

• user2252031: Your assumptions are right. I'll edit my post.
• user2252031: Ok this works perfect. I actually experimented both ways: 1. with the WHERE A = (SELECT MAX(A) ..... 2. with an extra max(pkey) and extra JOIN around my original query. Turns out there's no real difference in performance. Option 1 as you suggested is much simpler in writing though.

Answer 2

Instead of GROUP BY you can use a subquery ORDER BY A DESC, pkey DESC and LIMIT the result to two records:

select t1.pkey, t1.uid, t1.A, t1.B, t1.C
from mytable t1
inner join (select pkey, a
 from mytable
 order by a desc, pkey desc
 limit 2) t2
on t1.pkey = t2.pkey
;

This is the result:

| pkey | uid | A | B | C |
|------|-----|---|---|------------------|
| 101 | 1 | 5 | 3 | should find this |
| 103 | 2 | 5 | 2 | should find this |

Rextester here

• Rick James: This has lost the GROUP BY uid; was it important?
• user2252031: this method doesn't seem to work in any case.
• McNets: still giving me the desired result, rextester.com/MJMFG71736 what do you mean?