April 29, 2009 at 11:20 am
Comments posted to this topic are about the item Split by a row (count) number of a query results
May 22, 2009 at 8:39 am
If I'm not mistaken, since this uses the identity column, it might not always produce the same number of columns. For example, if you deleted a couple rows in the middle, it would still think those rows were there using your between statement.
Why not use top? For example, if you want chucks of 1000 records:
select top 1000 * from a_table order by ID
You can then capture the last identity that was selected and your next statement would be:
select top 1000 * from a_table where ID > @lastident order by ID
May 26, 2009 at 1:14 am
... or by parameterizing the top statement maybe...
declare @i int
set @i = 1000
select top(@i) * from tablename order by ID
May 26, 2009 at 4:24 am
Here's the ultimate method.
I tried to create an example using the identity column but it required additional queries which wasn't so efficient. Still, it was more efficient than creating temp tables.
Here's a method that works with SQL 2005 using the row_number() function. I tested it and it works flawlessly. Feedback would be appreciated.
DECLARE @numberofitemsperpage INT
DECLARE @numberofpages INT
DECLARE @currentpage int
--change the following two variables to your requirements
SET @numberofitemsperpage = 10
SET @numberofpages = 5
SET @currentpage =0
WHILE @currentpage = @currentpage * @numberofitemsperpage +1 AND Row <= (@currentpage+1) * @numberofitemsperpage
IF @@ROWCOUNT = 0 BREAK
SET @currentpage = @currentpage +1
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply