| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 04/17/2003 : 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
India
437 Posts |
Posted - 04/17/2003 : 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. |
 |
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 04/17/2003 : 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 |
|
|
|
samsekar
Constraint Violating Yak Guru
India
437 Posts |
Posted - 04/17/2003 : 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. |
|
|
|
ValterBorges
Flowing Fount of Yak Knowledge
USA
1429 Posts |
Posted - 04/17/2003 : 08:58:01
|
I know that is why I stated
quote:
in the case of a simple select 1'st R rows
|
|
|
|
samsekar
Constraint Violating Yak Guru
India
437 Posts |
Posted - 04/17/2003 : 09:01:24
|
Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
United Arab Emirates
1456 Posts |
Posted - 04/17/2003 : 11:21:54
|
also, any such query is not guaranteed to return consistent results unless you use an ORDER BY.
OS
|
|
|
|
1fred
Posting Yak Master
Canada
158 Posts |
Posted - 04/17/2003 : 11:30:35
|
The best way should be to query an auto increment index.
Select * from Table where id between (@n and (@n+@r))
|
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 04/17/2003 : 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 |
|
|
|
Blastrix
Posting Yak Master
208 Posts |
|
|
Stoad
Freaky Yak Linguist
*
1983 Posts |
Posted - 08/27/2003 : 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? |
|
|
|
AlTodd
Starting Member
2 Posts |
Posted - 06/30/2005 : 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 |
|
|
|
aakash
Starting Member
USA
8 Posts |
Posted - 10/19/2005 : 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 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 10/19/2005 : 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) |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 10/19/2005 : 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. |
|
|
|
aakash
Starting Member
USA
8 Posts |
Posted - 10/19/2005 : 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 |
|
|
| |
Topic |
|
|
|
Retrieve rows N to N+R in a select
