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

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?

• Another approach: mysql.rjweb.org/doc.php/groupwise_max#using_variables May 9, 2017 at 18:48
• 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? May 9, 2017 at 18:55
• i added some more records for clarification May 9, 2017 at 19:27

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

• Your assumptions are right. I'll edit my post. May 9, 2017 at 18:23
• 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. May 9, 2017 at 19:19

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

• This has lost the `GROUP BY uid`; was it important? May 9, 2017 at 18:46
• this method doesn't seem to work in any case. May 9, 2017 at 19:29
• still giving me the desired result, rextester.com/MJMFG71736 what do you mean? May 9, 2017 at 19:47