Top 10 selection

[dcorrea]

I have the following script:

SELECT t1.*
FROM [MyTable] t1
WHERE t1.TOTAL In (select top 10 t2.TOTAL from [MyTable] t2 where t2.FACILITY = t1.FACILITY);

which provides me with the top ten list of each facility, but not of their totals, but rather their id.

My table is as such

ID FACILITY TOTAL
1 A 100
2 A 102
3 A 97
. . .
. . .
205 E 115
206 E 77

I am needing to retreive the top 10 (greatest #s) totals for each facility on one query. Any suggestions?

Thanks,

dc

 

[amorous]

Try this:

SELECT t1.*
FROM [MyTable] t1
WHERE t1.TOTAL In (select top 10 t2.TOTAL from [MyTable] t2 where t2.FACILITY = t1.FACILITY ORDER BY t2.TOTAL DESC);

VJ

 

[dcorrea]

Thank you! It worked!

dc