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

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

  • 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

  • ... or by parameterizing the top statement maybe...

    declare @i int

    set @i = 1000

    select top(@i) * from tablename order by ID

  • 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


    SET @currentpage = @currentpage +1


Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply