SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Retrieve rows N to N+R in a select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 04/17/2003 :  07:47:47  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

India
437 Posts

Posted - 04/17/2003 :  08:30:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 04/17/2003 :  08:42:17  Show Profile  Reply with Quote
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

India
437 Posts

Posted - 04/17/2003 :  08:48:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 04/17/2003 :  08:58:01  Show Profile  Reply with Quote
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

India
437 Posts

Posted - 04/17/2003 :  09:01:24  Show Profile  Reply with Quote


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

United Arab Emirates
1456 Posts

Posted - 04/17/2003 :  11:21:54  Show Profile  Visit mohdowais's Homepage  Reply with Quote
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

Canada
158 Posts

Posted - 04/17/2003 :  11:30:35  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 04/17/2003 :  21:09:39  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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 - 04/18/2003 :  12:01:55  Show Profile  Reply with Quote
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 - 08/27/2003 :  15:37:45  Show Profile  Visit Stoad's Homepage  Reply with Quote
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 - 06/30/2005 :  13:57:39  Show Profile  Reply with Quote
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

USA
8 Posts

Posted - 10/19/2005 :  02:46:31  Show Profile  Reply with Quote
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

India
22744 Posts

Posted - 10/19/2005 :  02:52:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 10/19/2005 :  09:12:07  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 10/19/2005 :  09:17:36  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

USA
8 Posts

Posted - 10/19/2005 :  13:43:46  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000