Help with Selecting Top - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Help with Selecting Top

Author

Topic

Lopaka
Starting Member

48 Posts

Posted - 2006-12-01 : 19:26:51

I trying to query the top 4 records ranked by the AMT field Desc. for all three states. Can anyone help me with this statement?

Here is some test data:

CREATE TABLE #TEMP(
STATE CHAR(2),
ENAME CHAR(5),
AMT MONEY)

INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('WA', 'NAME1', 1)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('WA', 'NAME2', 2)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('WA', 'NAME3', 3)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('WA', 'NAME4', 3)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('WA', 'NAME5', 4)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('WA', 'NAME6', 5)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('WA', 'NAME7', 5)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('OR', 'NAME1', 1)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('OR', 'NAME2', 2)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('OR', 'NAME3', 3)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('OR', 'NAME4', 3)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('OR', 'NAME5', 4)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('OR', 'NAME6', 5)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('OR', 'NAME7', 5)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('CA', 'NAME1', 1)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('CA', 'NAME2', 2)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('CA', 'NAME3', 3)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('CA', 'NAME4', 3)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('CA', 'NAME5', 4)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('CA', 'NAME6', 5)
INSERT INTO #TEMP(STATE, ENAME, AMT) VALUES('CA', 'NAME7', 5)

Thank you

Robert R. Barnes

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-01 : 19:47:04

thanks for the sample data!

if you want the top 4 Amounts including ties this should work:


select	state, ename, amt
from	#temp t
where	amt in (select top 4 amt 
		from #temp 
		where state = t.state 
		order by amt desc)
order by state, amt desc

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources