select top N rows for a given field

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

SQL Server Development (2000)

select top N rows for a given field

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2009-08-11 : 12:58:53

Hi,

i have a table similar to following:

MR# Sequence
123 1
123 2
123 3
456 1
456 2
456 3
456 4
456 5
456 6
456 7
456 8
789 1
789 2
789 3
789 4
789 5
789 6

i want to get back up to only the first 5 sequence numbers for each MR# as follows:

MR# Sequence
123 1
123 2
123 3
456 1
456 2
456 3
456 4
456 5
789 1
789 2
789 3
789 4
789 5

i am having some difficulty doing this. Does anyone have any good ideas on how to extract this data?

thanks
Scott

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-11 : 13:03:19

SELECT TOP 5?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

scabral7
Yak Posting Veteran

57 Posts

Posted - 2009-08-11 : 13:07:18

But top 5 will give me just the top 5 rows of the table, i want the top 5 rows for each MR# field, so for each MR#, i want only the top 5 rows (or less if there are less than 5 rows).

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-11 : 13:17:46

ok...I see now

DECLARE @n int

SET @n = 5

SELECT * FROM yourTable WHERE Sequence <= @n???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

scabral7
Yak Posting Veteran

57 Posts

Posted - 2009-08-11 : 15:15:07

sorry,

i thought that would work, but the sequence numbers are not always in order.

For example they could be like this:

MR# Sequence
123 12
123 50
123 65

i basically need to get top 5 rows for each MR# group. Not sure if that explains it better...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-11 : 16:05:49

Try this...Replace <urtable> with your actual table name.

select * from 
(
select *, ( select count(*)
                 from   <urtable> t1
                 where  t1.MR# = t.MR#
                    and  t1.Sequence <= t.Sequence) as RowNumber
from   <urtable> t
) t2
where t2.RowNumber <= 5

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-14 : 02:01:15

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