How to skip sets of records - 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)

How to skip sets of records

Author

Topic

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-06-29 : 21:08:15

Lets say I have this select statement:

select top 6 p.id from product p where status = 0 order by newid()

but now I want to select sets. So I want to be able to lets say skip the first x number of records based on an incoming parameter.

So lets say that incoming parameter value is 3 which means I'm really wanting to start 3 in from the 6. How can I essentially do a pointer like this in SQL?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 21:13:44

you can make use or row_number() function


declare @start int

select @start = 3

select id
from   (
           select id, row_no = row_number() over (order by id)
           from   product
           where  status = 0
       ) p
where  row_no >= @start
and    row_no <= @start + 6

KH
[spoiler]Time is always against us[/spoiler]

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-06-29 : 21:15:18

what if I don't want to use functions

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-06-29 : 21:19:48

And I don't get it, I should receive 3 back, not 7 which is what yours gives me back. Because if I start at 3 with a total of 6 records I should get records 3-6 back

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-06-29 : 21:22:51

Is there anything other than this stupid row number I can use or is that the only way in SQL? If so, I still don't see this working for me. Sorry, I just can't stand SQL sometimes :(

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-06-29 : 21:23:50

I plan on setting the start and stuff in C# and then passing into SQL...i'm not going to be using params in a stored proc for this one as we're using parameterized SQL in our C#. So if there is a better way to do this in SQL if I'm passing in a start param, I'm all ears.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 21:25:44

quote:
Originally posted by CoffeeAddict

And I don't get it, I should receive 3 back, not 7 which is what yours gives me back. Because if I start at 3 with a total of 6 records I should get records 3-6 back

try


where  row_no >= @start
and    row_no <= 6

KH
[spoiler]Time is always against us[/spoiler]

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-06-29 : 21:31:00

I did that but a problem.

If I run this:

select top 6 p.id from product p where status = 0 order by newid()

I got 6 IDs:

7857
8362
9070
8533
7466
8030

If I run this, I get what I want but they are not even the last 4 IDs from above, they are totally different!

declare @start int

select @start = 3

select id
from (
select id, row_number() over (order by newid()) as row_no
from product
where status = 0
) p
where row_no >= @start
and row_no <= 6

I get these IDs back which aren't even in the first set of numbers from the first query:

9191
2533
8267
8079

it's probably because in my first query, I'm using top

But lets say start is 0, then i want all 6. How can I encorporate sort of the first scenario into this. If I have no start, give me all 6?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-29 : 21:32:50

you are expecting both query give you same result ? Do you know that ORDE BY newid() will order the result in random manner ?

KH
[spoiler]Time is always against us[/spoiler]

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-06-29 : 21:34:00

ah, that's right.

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-06-29 : 21:58:45