Select top n records for each param

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)

Select top n records for each param

Author

Topic

vivsriaus
Starting Member

8 Posts

Posted - 2009-02-23 : 17:48:02

Sample table:

Col1 Col2
-----------
ARR 156
ARR 123
ARR 234
ARR 345
Oscar 111
Oscar 222
Oscar 333
Oscar 444
Oscar 555
...
...
...

Problem: I want to select top 3 records for each Col1 value. The resulting table should look something like this:

Col1 Col2
-----------
ARR 156
ARR 123
ARR 234
Oscar 111
Oscar 222
Oscar 333

Any help will be much appreciated!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 18:02:11

[code]Select Col1,Col2
from
(Select ROW_NUMBER() OVER(Partition by Col1 Order by Col1) as seq,
* from Table)Z
Where Z.seq<=3[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-24 : 03:00:42

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

vivsriaus
Starting Member

8 Posts

Posted - 2009-02-24 : 13:46:46

Thanks for the help!

I tried doing this, but for some reason I'm not able to get the correct result. I end up getting only one entry each for col1, even after specifying the Where clause correctly. I tried seeing the results of the inner Select clause alone, and I did see the row number tagged on to each of the record as expected. I don't know why the Where predicate isn't applied, or if I'm missing something here.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 13:49:14

Can you show us your query?

vivsriaus
Starting Member

8 Posts

Posted - 2009-02-24 : 14:46:14

Sure! Here is my query:

Select Col1, Col2, Col3
from
(Select ROW_NUMBER() OVER(Partition by Col1 ORDER BY Col3 DESC) as RowNumber, Col1, Col2, Col3
from Table)T
Where T.RowNumber <= 3

Now, when I run the inner query alone:
Select ROW_NUMBER() OVER(Partition by Col1 ORDER BY Col3 DESC) as RowNumber, Col1, Col2, Col3
from Table

I get all the records, partitioned by Col1 with row number for each, as expected.

vivsriaus
Starting Member

8 Posts

Posted - 2009-02-24 : 14:54:33

I'm getting this to work if I change the query to something like this

Select RowNumber, Col1, Col2, Col3
from
(Select ROW_NUMBER() OVER(Partition by Col1 ORDER BY Col3 DESC) as RowNumber, Col1, Col2, Col3
from Table)T
Where RowNumber <= 3

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 14:56:11

quote:
Originally posted by vivsriaus

I'm getting this to work if I change the query to something like this

Select RowNumber, Col1, Col2, Col3
from
(Select ROW_NUMBER() OVER(Partition by Col1 ORDER BY Col3 DESC) as RowNumber, Col1, Col2, Col3
from Table)T
Where RowNumber <= 3

Because you haven't shown us COL3 in original post.

vivsriaus
Starting Member

8 Posts

Posted - 2009-02-24 : 16:30:45

sodeep: I added Col3 just to make my statement more clearer and broad. Even if I had included just Col1 and Col2, it didn't work for some reason.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 17:02:15

quote:
Originally posted by vivsriaus

sodeep: I added Col3 just to make my statement more clearer and broad. Even if I had included just Col1 and Col2, it didn't work for some reason.

What didn't work? Well that was correct answer according to your requirement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-25 : 10:00:06

quote:
Originally posted by vivsriaus

I'm getting this to work if I change the query to something like this

Select RowNumber, Col1, Col2, Col3
from
(Select ROW_NUMBER() OVER(Partition by Col1 ORDER BY Col3 DESC) as RowNumber, Col1, Col2, Col3
from Table)T
Where RowNumber <= 3

how is this different from what you posted earlier? you've just included the extra column in select

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources