Have Geek Brain

SQL partial max

Dealing with the next SQL problem.

Suppose a 3D table with records (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.

on the first element and then returns the maximum q value of the same type, that is, there are 10 As and 11 Bs for header 2, so return 11.

The element "max" (A in case 1, B in case 2 and C in case 3) is irrelevant. I just need to get out of the header and this max value.

It shouldn't be too hard, but I can't seem to get it to work. Using MS Access, but can use SQL internally.

+2


a source to share


2 answers 


SELECT  h, MAX(cnt)
FROM    (
        SELECT  h, SUM(q) AS cnt
        FROM    mytable
        GROUP BY
                h, t
        ) sq
GROUP BY
        h


+2


a source


Something like this should work:

select h, max(q) from table
group by h


Edit: Nothing ... I misunderstood. Quassnoi's solution should work.

0


a source






More articles:


All Articles