Maximum Value By Department - SQL Help

[warrenk]

I am trying to run a SQL that selects the maximum count by department...

For example, below is TABLE1 with the following fields TOTAL, DEPART, BUYER. The results should be grouped by DEPART. Any help greatly appreciated!

TOTAL, DEPART, BUYER
1, 1254, 10740431,
37, 1254, 12680081,
1, 1255, 10740431,
30, 1255, 12680081,
1, 1256, 10740431,
36, 1256, 12680081,

I want to return:

37 1254 12680081
30 1255 12680081
36 1256 12680081

Warren

 

[r937]

select TOTAL
     , DEPART
     , BUYER 
  from TABLE1 as T
 where TOTAL = 
       ( select max(TOTAL)
           from TABLE1
          where DEPART = T.DEPART )

r937.com | rudy.ca