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)
 Retrieve rows N to N+R in a select

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-17 : 07:47:47
Nicolas writes "How can you retrieve a particular range of rows (lets say R rows starting from row N)?

I though I could use the SELECT TOP n syntax however the n cannot be a parameter (I want this in a stored procedure so ideally I would write something like SELECT TOP @R, but that does not work).

An alternative could be to create a query that returns the row index in a column, so I can write something like
SELECT * FROM
(
SELECT RowIndex = ???,*
FROM MY_TABLE
) AS QRY
WHERE QRY.RowIndex BETWEEN @N AND (@N + @R)

But I don't know how to that either...

(SQL Server 2000)

Thanks, Nicolas"

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-17 : 08:30:38
Something like this..

declare @R char(1), @N char(1), @str varchar(8000)
set @N=5 -- starting point
set @R=3 -- no of rows
select * from northwind.dbo.employees
set @str = 'select top ' + @R + ' * from northwind.dbo.employees
where employeeid not in (select top ' + @N + ' employeeid from northwind.dbo.employees)'
print @str
exec (@str)

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.
Go to Top of Page

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-17 : 08:42:17
you can use

SET ROWCOUNT @R
instead of SELECT TOP @R in the case of a simple select 1'st R rows

Then your select statement



Edited by - ValterBorges on 04/17/2003 08:49:13
Go to Top of Page

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-17 : 08:48:25
quote:

you can use

SET ROWCOUNT @R

Then your select statement


Hey volter, I belive your solution will not solve his problem, he needs R rows starting from row N Where R & N are parameters.

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.
Go to Top of Page

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-17 : 08:58:01
I know that is why I stated

quote:
in the case of a simple select 1'st R rows





Go to Top of Page

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-17 : 09:01:24


Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-04-17 : 11:21:54
also, any such query is not guaranteed to return consistent results unless you use an ORDER BY.

OS

Go to Top of Page

1fred
Posting Yak Master

158 Posts

Posted - 2003-04-17 : 11:30:35
The best way should be to query an auto increment index.

Select * from Table where id between (@n and (@n+@r))

Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-17 : 21:09:39
From another post of mine:

Here's a way to return rows @a through @b from a table or query -- but only if it is ordered by a unique field:

(assume we are ordering by the field "ID")

declare @startingID int;
declare @a int;
declare @b int;

set @a = 200
set @b = 250

-- @a = starting row #
-- @b = ending row #

set rowcount @a
select @startingID = ID from yourtable order by ID ASC

set @b = @b - @a

set rowcount @b
select * from yourtable where ID >= @startingID order by ID

set rowcount 0

---

Something to play around with, anyway ... should be fairly efficient though it has to make 2 passes through the data, and it doesn't use temp tables or updates or anything like that. Also should be more efficient than the SELECT TOP x FROM (Top y) techniques.

has anyone else used this techinque? any comments?

NOTE: this also can be adjusted if the ordering is by more than 1 column, but is a little more complex. let me know if anyone needs to see that.

- Jeff
Go to Top of Page

Blastrix
Posting Yak Master

208 Posts

Posted - 2003-04-18 : 12:01:55
Check out this article from 4guysfromrolla.com. Technically it's an ASP site, but this article deals with paging query results:

http://www.aspfaqs.com/webtech/062899-1.shtml

Steve

Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-27 : 15:37:45
Having not much to do at the time I've visited Steve's link.
And what I see... the paging procedure starts with

--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
Name varchar(50),
Price currency
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price

Well... interesting, the entire table is copied into temp table...
Is not this operation a bit overloading for the case of an huge table
or it does not matter?
Then, that ORDER BY in bold... Is it just a carefree style or I missed
something in here?
Go to Top of Page

AlTodd
Starting Member

2 Posts

Posted - 2005-06-30 : 13:57:39
heres an sp that can page and sort on a given column either ascending or descending, based on jeff's work. It neither uses cursors or stored procedures. There's a bit more leg work and some replication for the ASC and DESC functionality, but id personally rather do that than use expensive cursors / temp tables and have real compiled SPs - not generated and exec-ed sql

Alter Procedure TEST_Paging
@pageNum int, @pageSize int, @sortColumn varchar(50), @sortAscending bit,
@rowCount int out,
@pageCount int out
As

SET NOCOUNT ON

if @pageNum = 0 or @pageSize = 0 raiserror('@pageNum and @pageSize must both be greater than 0', 16, 1)
declare @startRow int
declare @endRow int

set @startRow = @pageSize * (@pageNum - 1)
set @endRow = @pageSize * @pageNum

select @rowCount = count(*)
from Note
where Title <> ''

declare @d as decimal(8,2)
Set @d = @rowCount
if(@d > 0 and @pageSize > 0) set @pageCount = ceiling(@d / @pageSize)
else set @pageCount = 0


-- for each column in your sort, you need a variable to hold
-- the "starting values". In our case, we need two:
declare @startingValue varchar(200)
declare @startingID int


-- get the starting date and starting ID to return results:
if @sortAscending = 1
begin
if(@startRow > 0) set rowcount @startRow
select @startingValue =
case @sortColumn
When 'Title' then Title
When 'NoteText' then NoteText
When 'CreatedDate' then convert(varchar(23), CreatedDate, 120)
end
, @startingID = NoteID
from Note
where Title <> ''
order by
case @sortColumn
when 'Title' then Title
when 'NoteText' then NoteText
when 'CreatedDate' then convert(varchar(23), CreatedDate, 120)
end
asc, NoteID asc
end
else
begin
if(@startRow > 0) set rowcount @startRow
select @startingValue =
case @sortColumn
When 'Title' then Title
When 'NoteText' then NoteText
When 'CreatedDate' then convert(varchar(23), CreatedDate, 120)
end
, @startingID = NoteID
from Note
where Title <> ''
order by
case @sortColumn
when 'Title' then Title
when 'NoteText' then NoteText
when 'CreatedDate' then convert(varchar(23), CreatedDate, 120)
end
desc, NoteID desc
end

-- find out how many rows to return, and set the rowcount:
set @endRow = @endRow - @startRow
set rowcount @endRow


-- now return the results:
if @sortAscending = 1
begin
select NoteID, Title, NoteText, CreatedDate
from Note
where
(@startRow = 0 OR
(@sortColumn = 'Title' AND ((Title = @startingValue And NoteID > @startingID) OR Title > @startingValue) ) OR
(@sortColumn = 'NoteText' AND ((NoteText = @startingValue And NoteID > @startingID) OR NoteText > @startingValue) ) OR
(@sortColumn = 'CreatedDate' AND ((convert(varchar(23), CreatedDate, 120) = @startingValue And NoteID > @startingID)
OR convert(varchar(23), CreatedDate, 120) > @startingValue) ))
And Title <> ''
order by
case @sortColumn
when 'Title' then Title
when 'NoteText' then NoteText
when 'CreatedDate' then convert(varchar(23), CreatedDate, 120)
end
asc, NoteID asc
end
else
begin
select NoteID, Title, NoteText, CreatedDate
from Note
where
(@startRow = 0 OR
(@sortColumn = 'Title' AND ((Title = @startingValue And NoteID < @startingID) OR Title < @startingValue) ) OR
(@sortColumn = 'NoteText' AND ((NoteText = @startingValue And NoteID < @startingID) OR NoteText > @startingValue) ) OR
(@sortColumn = 'CreatedDate' AND ((convert(varchar(23), CreatedDate, 120) = @startingValue And NoteID < @startingID)
OR convert(varchar(23), CreatedDate, 120) < @startingValue) ))
And Title <> ''
order by
case @sortColumn
when 'Title' then Title
when 'NoteText' then NoteText
when 'CreatedDate' then convert(varchar(23), CreatedDate, 120)
end
desc, NoteID desc
end

-- clean up:
set rowcount 0
SET NOCOUNT OFF
RETURN



and some test calls
declare @myRowcount int, @myPageCount int
exec TEST_Paging 1, 3, 'Title', 1, @rowcount = @myRowcount out, @pageCount = @myPageCount out
exec TEST_Paging 2, 3, 'Title', 1, @rowcount = @myRowcount out, @pageCount = @myPageCount out
exec TEST_Paging 3, 3, 'Title', 1, @rowcount = @myRowcount out, @pageCount = @myPageCount out
select top 9 NoteID, Title, NoteText, CreatedDate, Title + convert(varchar, NoteID)
from note
where title<>''
order by Title asc, NoteID asc
select @myRowcount As [RowCount], @myPageCount as PageCount

GO

declare @myRowcount int, @myPageCount int
exec TEST_Paging 1, 3, 'CreatedDate', 0, @rowcount = @myRowcount out, @pageCount = @myPageCount out
exec TEST_Paging 2, 3, 'CreatedDate', 0, @rowcount = @myRowcount out, @pageCount = @myPageCount out
exec TEST_Paging 3, 3, 'CreatedDate', 0, @rowcount = @myRowcount out, @pageCount = @myPageCount out
select top 9 NoteID, Title, NoteText, CreatedDate
from note
where title<>''
order by convert(varchar(23), CreatedDate, 120) desc, NoteID desc
select @myRowcount As [RowCount], @myPageCount as PageCount

GO
Go to Top of Page

aakash
Starting Member

8 Posts

Posted - 2005-10-19 : 02:46:31
quote:
Originally posted by jsmith8858

From another post of mine:

Here's a way to return rows @a through @b from a table or query -- but only if it is ordered by a unique field:

(assume we are ordering by the field "ID")

declare @startingID int;
declare @a int;
declare @b int;

set @a = 200
set @b = 250

-- @a = starting row #
-- @b = ending row #

set rowcount @a
select @startingID = ID from yourtable order by ID ASC

set @b = @b - @a

set rowcount @b
select * from yourtable where ID >= @startingID order by ID

set rowcount 0

---

Something to play around with, anyway ... should be fairly efficient though it has to make 2 passes through the data, and it doesn't use temp tables or updates or anything like that. Also should be more efficient than the SELECT TOP x FROM (Top y) techniques.

has anyone else used this techinque? any comments?

NOTE: this also can be adjusted if the ordering is by more than 1 column, but is a little more complex. let me know if anyone needs to see that.

- Jeff




Another way to do this in SQL Server 2005 is using the ROW_NUMBER rank function in a subquery:

SELECT * from
(SELECT ROW_NUMBER() OVER(ORDER by mytable.id ASC) as rowid, id, username from mytable) dt
WHERE dt.rowid >= @a AND dt.rowid < @b

-Aakash
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 02:52:23
But ROW_NUMBER() is not supported in SQL Server 2000
So the only way is Pagination
http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-19 : 09:12:07
This is a general method of returning R rows from position N:
select	YourTable.*
from YourTable
inner join YourTable as Ordinals
on YourTable.SortKey >= Ordinals.SortKey
group by YourTable.[AllFields]
having count(*) between @N and (@N + @R)
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-19 : 09:17:36
blindman -- works good for small sets of rows; becomes extremely inefficient as your table size increases.

For that general technique, to avoid the grouping of all columns (adds more inefficiency since the groups must be calculated) you can use:

select T1.*
from YourTable T1
where
(select count(*) from YourTable T2 where T2.SortKey <= T1.SortKey) between @N and (@N+@R)

However, that still has the same general inefficency.
Go to Top of Page

aakash
Starting Member

8 Posts

Posted - 2005-10-19 : 13:43:46
quote:
Originally posted by madhivanan

But ROW_NUMBER() is not supported in SQL Server 2000
So the only way is Pagination
http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

Madhivanan

Failing to plan is Planning to fail



Yes, it only works in SQL 2005. I would highly recommend using ROW_NUMBER() in SQL2005 - it is efficient, and also portable (since its part of the ANSI SQL-2003 standard)


-Aakash
Developer, SQL Server engine
Go to Top of Page
   

- Advertisement -