Select for the Max Records

[schafjoe]

Everybody,
I have the following table. All of the fields are of the Interger type. The records that I need is the maximum value of the Score Divided by the MaxScr for each Period, Region, Comm and Elem grouping.

Period Region Comm Elem MaxScr Score
9 2 6 2 23 13
9 2 6 2 23 14
9 2 6 2 23 19
9 2 6 3 12 4
9 2 6 3 12 8
9 2 6 3 12 9
9 2 6 5 4 3
9 2 6 5 4 4
9 2 6 5 6 3
9 2 6 6 21 11
9 2 6 6 21 14
9 2 6 6 21 15
9 2 6 6 21 18
9 2 6 7 2 2
9 2 6 7 5 4
9 2 6 7 7 0
9 2 6 8 15 15
9 2 6 8 20 18
9 2 6 8 25 9

and here is the results that I need.
Period Region Comm Elem MaxScr Score
9 2 6 2 23 19
9 2 6 3 12 9
9 2 6 5 4 4
9 2 6 6 21 18
9 2 6 7 2 2
9 2 6 8 15 15

 

[swampBoogie]

select * from t as tq
where score <> 0
and (MaxScr+0.0)/(Score+0.0)
in (select max((MaxScr+0.0)/(Score+0.0))
from t
where period = tq.period
and region = tq.region
and Comm = tq.Comm
and Elem = tq.Elem
and Score <> 0)

 

[MeanGreen]

I am not sure what you are really asking for, but is this close to what you want?

select period,region,comm,elem,maxscr,max(score) score, convert(float(8,3),(max(score)))/convert(float(8,3),maxscr) avg from mytable
group by period,region,comm,elem,maxscr

Hope this helps.
MeanGreen