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?

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

  • Your assumptions are right. I'll edit my post. – user2252031 May 9 '17 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. – user2252031 May 9 '17 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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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