Select a range of Rows

[Paul Johnson]

Anyone know if there is a way to select a range of rows. Much the same
as using the Select Top 10 From ....
Something Like Select Range 10 to 20 FROM .... ?

I have searched and searched for something that will do this but can
find anything

 

[Miha Markic [MVP C#]]

Hi Paul,

You mean from database?
I would go with a WHERE clause where I would specify the first record to be
fetched, like ID > 100 and a TOP keyword in front
Assuming SQLServer is the database.

 

[Dumitru Sbenghe]

This solution works only if the select contains the Order By ID clause,
which is not always possible.

Generally speaking, we don't have a clean sql statement solution using Sql
Server (as using Oracle for example); only with stored procedures.

The ADO.NET 2.0 will have ExecutePageReader to solve this problem. I heard
that it use server side cursors, which doesn't smell like a good solution
for me.

Dumitru

Hi Paul,

You mean from database?
I would go with a WHERE clause where I would specify the first record to be
fetched, like ID > 100 and a TOP keyword in front
Assuming SQLServer is the database.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Anyone know if there is a way to select a range of rows. Much the same as
using the Select Top 10 From ....
Something Like Select Range 10 to 20 FROM .... ?

I have searched and searched for something that will do this but can find
anything

 

[Miha Markic [MVP C#]]

This solution works only if the select contains the Order By ID clause,
which is not always possible.

Or if ID is an autoinc pk (without order by clause). Sure, it is not a
generic solution.
Another solution would be to store ranged pk's into a temporary order and
inner join them.

 

[Paul Johnson]

The Database can have rows deleted so the ids may be 1,2,3,5,7,9 so i
cannot us the id field to select the range.

It would be usefull if there were some sort of rowcounting var that
coulb be add to the select that incremented for each row in the select.

I.E somthing that does the following

SELECT *
FROM DataTable
WHERE @@RowCounter > 10 AND @@RowCounter < 20

does anything like this exist?

 

[Ryan Trudelle-Schwarz]

The Database can have rows deleted so the ids may be 1,2,3,5,7,9 so i
cannot us the id field to select the range.

It would be usefull if there were some sort of rowcounting var that
coulb be add to the select that incremented for each row in the
select.

I.E somthing that does the following

SELECT *
FROM DataTable
WHERE @@RowCounter > 10 AND @@RowCounter < 20
does anything like this exist?

One common solution is to do something like so:

SELECT TOP 10
x.*
FROM
x
WHERE
x.ID NOT IN (SELECT TOP 10 x.ID FROM x ORDER BY x.ID)
ORDER BY
x.ID

Basically, select the first 10 records which are not among the first 10 records
of the total set.

hth, Rya

 

[Jackie Goldstein]

Actually, the ExecutePageReader feature has been dropped from ADO.NET 2.0 –
mainly for the reason that you "smelled" J


Jackie

This solution works only if the select contains the Order By ID clause,
which is not always possible.

Generally speaking, we don't have a clean sql statement solution using Sql
Server (as using Oracle for example); only with stored procedures.

The ADO.NET 2.0 will have ExecutePageReader to solve this problem. I heard
that it use server side cursors, which doesn't smell like a good solution
for me.

Dumitru

Hi Paul,

You mean from database?
I would go with a WHERE clause where I would specify the first record to be
fetched, like ID > 100 and a TOP keyword in front
Assuming SQLServer is the database.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Anyone know if there is a way to select a range of rows. Much the same as
using the Select Top 10 From ....
Something Like Select Range 10 to 20 FROM .... ?

I have searched and searched for something that will do this but can find
anything