Top 10 Selections - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Top 10 Selections

Author

Topic

dcorrea
Starting Member

3 Posts

Posted - 2004-05-18 : 16:50:34

I currently have a table with 12 facilities A-L and 25 different totals for each facility.
Ex.
FACILITY TOTAL
A 10
A 12
A 8
. .
. .
L 11
L 10
L 9

I am needing a query that would allow me to review the results of the top 10 TOTALS (Desc) for each facility (120 entries total) in the same query result.

Any suggestions?

Thanks,

dc

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-18 : 17:04:00

select *
from tbl t1
where Total in (select top 10 t2.Total from tbl t2 where t2.Facility = t1.Facility)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dcorrea
Starting Member

3 Posts

Posted - 2004-05-19 : 11:52:44

This is the script that I have:

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

Where do I plug in the t1 & t2? Can you explain to me the significance of the t1 & t2?

Thanks in advance for your time and assistance.

dc

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-19 : 12:06:33

They are aliases for the table (think you can do that in access
SELECT t1.*
FROM [MyTable] t1
WHERE t1.TOTAL In (select top 10 t2.TOTAL from [MyTable] t2 where t2.FACILITY = t1.FACILITY);

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dcorrea
Starting Member

3 Posts

Posted - 2004-05-19 : 12:20:36

Thanks! It works!

dc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources