So given
| a | b | c |
|---|---|---|
| 1 | 1 | 3 |
| 1 | 2 | 8 |
| 1 | 3 | 6 |
| 1 | 3 | 7 |
I want to query the rows with biggest b, however I dont know that biggest b would be 3 in this case. Same query should return the last two rows here aswell:
| a | b | c |
|---|---|---|
| 1 | 0 | 3 |
| 1 | 1 | 8 |
| 1 | 4 | 6 |
| 1 | 4 | 7 |
What I do is quite literally look the biggest value up and then query for it.
SELECT a,c FROM [table] t1
INNER JOIN (SELECT a, MAX(b) as maxb FROM [table] GROUP BY a) t2 ON t2.a = t1.a
WHERE t1.b = t2.maxb
It feels so clunky to do, although it feels like a simple concept. Is this the real solution?