Getting TOP 4 For Each - 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 2005 Forums

Transact-SQL (2005)

Getting TOP 4 For Each

Author

Topic

mnielsen
Starting Member

17 Posts

Posted - 2009-08-09 : 08:20:13

Hi Everyone,

I have a data for a automated test that runs from city to city for the following cities:

1. Originating City
London
Buenos Aires
Newark
Piscataway
Hong Kong

2. Destination City
London
Buenos Aires
Newark
Piscataway
Hong Kong

I would like to get the top 4 test results for each originating city going to each destination city. I want to display the results like:

London
------
Buenos Aires DATA DATA
Buenos Aires DATA DATA
Buenos Aires DATA DATA
Buenos Aires DATA DATA
Piscataway DATA DATA
Piscataway DATA DATA
Piscataway DATA DATA
Piscataway DATA DATA
Newark DATA DATA
Newark DATA DATA
Newark DATA DATA
Newark DATA DATA
Hong Kong DATA DATA
Hong Kong DATA DATA
Hong Kong DATA DATA
Hong Kong DATA DATA

Newark
------
Buenos Aires DATA DATA
Buenos Aires DATA DATA
Buenos Aires DATA DATA
Buenos Aires DATA DATA
Piscataway DATA DATA
Piscataway DATA DATA
Piscataway DATA DATA
Piscataway DATA DATA
London DATA DATA
London DATA DATA
London DATA DATA
London DATA DATA
Hong Kong DATA DATA
Hong Kong DATA DATA
Hong Kong DATA DATA
Hong Kong DATA DATA

Any help is greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 09:00:57

SELECT Col1, Col2, Col3 FROM (
SELECT Col1, Col2, Col3, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS recID
FROM Table1
) AS d
WHERE recID <= 4

N 56°04'39.26"
E 12°55'05.63"

mnielsen
Starting Member

17 Posts

Posted - 2009-08-09 : 09:07:05

Getting:

Msg 195, Level 15, State 10, Line 12
'ROW_NUMBER' is not a recognized function name.

mnielsen
Starting Member

17 Posts

Posted - 2009-08-09 : 09:32:21

Thinking about a cursor maybe? I'm not sure how to use that though :o(

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-09 : 09:36:58

You have posted in SQL Server 2005 forum so row_number() should work.
What version of SQL Server do you use?

No, you're never too old to Yak'n'Roll if you're too young to die.

mnielsen
Starting Member

17 Posts

Posted - 2009-08-09 : 09:39:18

2003

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-09 : 09:42:31

2003 can be the Windows Server not SQL Server.

What gives you SELECT @@VERSION

No, you're never too old to Yak'n'Roll if you're too young to die.

mnielsen
Starting Member

17 Posts

Posted - 2009-08-09 : 09:48:29

Oh....2000. What I read, this isn't supported in that version :o(

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-10 : 00:45:41

Hi try this once,

select identity(int,1,1)as rid, * into #temp from ur_table

select * from (
select t.rid,col1,(select count(col1) from #temp where rid <= t.rid and col1 = t.col1 )as cnt
from #temp t ) a
where a.cnt < = 4

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 01:22:06

see this link
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources