Return a range of row results from a SQL query

[happyIndian100]

How to return the Nth to Mth row of a particular query?

thanks in advance

 

[vongrunt]

Get M rows, skip first N-1 rows. If M is not big ("shallow paging") skipping can be done client-side.

See thread183-831071 for some ideas.

In SQL2005 this is much easier thanks to ranking functions.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[gmmastros]

There are several methods you can use to accomplish this. My favorite method is to create a table variable with an idenitity column. Your query would insert the data in to this temp table and then select out of the temp table only those rows you care about.

Example:

Create Procedure GetData
  @FirstRow Integer,
  @LastRow Integer
AS
SET NOCOUNT ON

Declare @Temp Table(RowId Integer Identity(1,1), Field1 DataTypeForField1, etc....)

Insert Into @Temp (Field1, etc...)
Select Field1, etc...
From   Table
Where  Condition=true
Order By SomeField

Select Field1, etc...
From   @Temp
Where  RowId Between @FirstRow And @LastRow
Order By RowId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

For one of these 2 statements there is no guarantee that the resultset will be in order of lastname, but which?

-- 1 --
create table1 (id int identity, field2 varchar(50))

insert into table1
select LastName
from SomeTable
Order by LastName
Go

-- 2 ---
select identity(int,1,1), LastName into table1
from SomeTable
Order by LastName

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Is that question somehow related to thread183-1219886?

(curious for obvious reasons).

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

yes, it's not a trick question I already forgot which one it was, I believe select into is guaranteed but I am not 100% sure

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

I'd like to get exact confirmation of that behaviour from somewhere - because SQL2000 has no ranking functions, identity table hack comes quite handy.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]