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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy