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?

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).

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