SQL partial max
- by Rob
Struggling with the following SQL problem
Assume a three dimensional table with entries (h,t,q)
1,A,20
1,A,10
1,B,5
2,A,10
2,B,3
2,B,8
3,C,50
4,A,10
etc.
I would like to extract
1,30
2,11
3,50
etc.
group by first element and then return the maximum q value of same type, i.e. for header number 2 there are 10 As and 11 Bs, so return 11
The "max" element (A in case 1, B in case 2 and C in case 3) is irrelevant, I just need to get out the header and that maximum value.
This shouldnt be too tricky, but cant get it to work. Using MS Access, but can use SQL within.
