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.
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 pointset @R=3 -- no of rowsselect * from northwind.dbo.employeesset @str = 'select top ' + @R + ' * from northwind.dbo.employeeswhere employeeid not in (select top ' + @N + ' employeeid from northwind.dbo.employees)'print @strexec (@str)Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-04-17 : 08:42:17
|
you can use SET ROWCOUNT @Rinstead of SELECT TOP @R in the case of a simple select 1'st R rowsThen your select statementEdited by - ValterBorges on 04/17/2003 08:49:13 |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-04-17 : 08:48:25
|
quote: you can use SET ROWCOUNT @RThen 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
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
|
|
|
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. |
|
|
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 |
|
|
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)) |
|
|
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 |
|
|
Blastrix
Posting Yak Master
208 Posts |
|
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 tableCREATE TABLE #TempItems( ID int IDENTITY, Name varchar(50), Price currency)-- Insert the rows from tblItems into the temp. tableINSERT INTO #TempItems (Name, Price)SELECT Name,Price FROM tblItem ORDER BY PriceWell... interesting, the entire table is copied into temp table...Is not this operation a bit overloading for the case of an huge tableor it does not matter?Then, that ORDER BY in bold... Is it just a carefree style or I missedsomething in here? |
|
|
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 sqlAlter Procedure TEST_Paging@pageNum int, @pageSize int, @sortColumn varchar(50), @sortAscending bit, @rowCount int out, @pageCount int outAsSET NOCOUNT ONif @pageNum = 0 or @pageSize = 0 raiserror('@pageNum and @pageSize must both be greater than 0', 16, 1)declare @startRow intdeclare @endRow intset @startRow = @pageSize * (@pageNum - 1)set @endRow = @pageSize * @pageNumselect @rowCount = count(*)from Note where Title <> ''declare @d as decimal(8,2)Set @d = @rowCountif(@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 = 1begin 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 ascendelsebegin 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 descend-- find out how many rows to return, and set the rowcount:set @endRow = @endRow - @startRow set rowcount @endRow -- now return the results:if @sortAscending = 1begin 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 ascendelsebegin 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 descend -- clean up:set rowcount 0 SET NOCOUNT OFFRETURN and some test callsdeclare @myRowcount int, @myPageCount intexec TEST_Paging 1, 3, 'Title', 1, @rowcount = @myRowcount out, @pageCount = @myPageCount outexec TEST_Paging 2, 3, 'Title', 1, @rowcount = @myRowcount out, @pageCount = @myPageCount outexec TEST_Paging 3, 3, 'Title', 1, @rowcount = @myRowcount out, @pageCount = @myPageCount outselect top 9 NoteID, Title, NoteText, CreatedDate, Title + convert(varchar, NoteID) from notewhere title<>''order by Title asc, NoteID ascselect @myRowcount As [RowCount], @myPageCount as PageCountGOdeclare @myRowcount int, @myPageCount intexec TEST_Paging 1, 3, 'CreatedDate', 0, @rowcount = @myRowcount out, @pageCount = @myPageCount outexec TEST_Paging 2, 3, 'CreatedDate', 0, @rowcount = @myRowcount out, @pageCount = @myPageCount outexec TEST_Paging 3, 3, 'CreatedDate', 0, @rowcount = @myRowcount out, @pageCount = @myPageCount outselect top 9 NoteID, Title, NoteText, CreatedDatefrom notewhere title<>''order by convert(varchar(23), CreatedDate, 120) desc, NoteID descselect @myRowcount As [RowCount], @myPageCount as PageCountGO |
|
|
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) dtWHERE dt.rowid >= @a AND dt.rowid < @b-Aakash |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
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.SortKeygroup by YourTable.[AllFields]having count(*) between @N and (@N + @R) |
|
|
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 T1where (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
8 Posts |
Posted - 2005-10-19 : 13:43:46
|
quote: Originally posted by madhivanan But ROW_NUMBER() is not supported in SQL Server 2000So the only way is Paginationhttp://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspxMadhivananFailing 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)-AakashDeveloper, SQL Server engine |
|
|
|
|
|
|
|