Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Split by a row (count) number of a query results Expand / Collapse
Author
Message
Posted Wednesday, April 29, 2009 11:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:34 AM
Points: 313, Visits: 177
Comments posted to this topic are about the item Split by a row (count) number of a query results
Post #707076
Posted Friday, May 22, 2009 8:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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
Post #722096
Posted Tuesday, May 26, 2009 1:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 28, 2011 4:41 AM
Points: 13, Visits: 45
... or by parameterizing the top statement maybe...

declare @i int
set @i = 1000
select top(@i) * from tablename order by ID

Post #723058
Posted Tuesday, May 26, 2009 4:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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 < @numberofpages
BEGIN
SELECT a.* FROM
(SELECT row_number() OVER (ORDER BY whatevercolumnyouwant) AS ROW, *
FROM
yourtablenamehere) a
WHERE ROW >= @currentpage * @numberofitemsperpage +1 AND Row <= (@currentpage+1) * @numberofitemsperpage
IF @@ROWCOUNT = 0 BREAK
SET @currentpage = @currentpage +1
END

Post #723153
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse