SQL partial max

Posted by Rob on Stack Overflow See other posts from Stack Overflow or by Rob
Published on 2010-04-30T15:17:35Z Indexed on 2010/04/30 15:17 UTC
Read the original article Hit count: 323

Filed under:
|

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about ms-access