«
»

I’ve never found a great solution for pagination in SQL 2000. I much prefer MySQL’s syntax: SELECT * FROM table LIMIT 100,10. That will get you 10 records starting at the 100th record. Why MS SQL still doesn’t offer an opposite to TOP, I don’t know. Yes, I’ve seen the double TOP method where you sort your data twice to end up with the last X rows. It works, but its not exactly elegant.

But I have come across a new method for pagination using some new 2005 features that is pretty neat. What I really like about this solution is that it gives you a count of the total number of records, which you usually don’t get with other pagination methods.

SELECT TOP #Val(arguments.maxRows)# * FROM
	(
	SELECT ROW_NUMBER() OVER (ORDER BY your sort order here) AS RowNumber, *, TotalRows=Count(*) OVER()
	FROM tableName WHERE your criteria here
	) _tmpInlineView
WHERE RowNumber >= <cfqueryparam value="#Arguments.startRow#">
ORDER BY your sort order here

This works great, is fast, and as a bonus includes the total number of rows!

This entry was posted on 12 November 2008 at 4:56 pm and is filed under SQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

6 Comments

  1. robert says:

    It’s a beauty! Thanks a lot!

    10 January 2009, 10:07 am

  2. Andrew Duvall says:

    I found this to be the best examples so far.  However, when i put this in place i realized i was missing records when using the ORDER BY if not the default primary key.  I found that in order to not exclude records that the ORDER BY needs to ALSO be on the last line as well.

    example:

    SELECT TOP 5 * FROM
        (
        SELECT  ROW_NUMBER() OVER (ORDER BY Product_Name) AS RowNumber, *, TotalRows=Count(*) OVER()
        FROM tbl_products
        ) _tmpInlineView
    WHERE RowNumber >= 5 ORDER BY Product_Name

    22 January 2010, 4:35 pm

  3. Ryan says:

    Thanks Andrew, I've updated the example code to include the second order by.

    23 January 2010, 9:13 pm

  4. Spencer says:

    This saved my life.  Thanks!

    19 March 2010, 2:12 pm

  5. negablog says:

    Great solution for getting total number of records using Count(*) Over(). The over() solves the purpose, without it aggregate function error will throw..
    Thanks a lot for writting.

    5 February 2013, 3:44 am

  6. Mike Henke says:

    Who is this guy?

    28 May 2013, 4:20 pm