Split by a row (count) number of a query results

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Bernabe Diaz » Split by a row (count) number of a query...

Split by a row (count) number of a query results

Rate Topic

Display Mode

Topic Options

Author

Message

diaz.bernabe

Posted Wednesday, April 29, 2009 11:20 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:10 AM
Points: 313, Visits: 168

Comments posted to this topic are about the item Split by a row (count) number of a query results

Post #707076

cengland0

Posted Friday, May 22, 2009 8:39 AM

Ten 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

b4ndt

Posted Tuesday, May 26, 2009 1:14 AM

Grasshopper

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

cengland0

Posted Tuesday, May 26, 2009 4:24 AM

Ten 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 »

Permissions