1

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?

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

2 Answers 2

0

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

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.