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?