Get total records while using ROW_NUMBER() for pagination

Discussion in 'Databases' started by tin2mon, Oct 16, 2010.

  1. #1
    I've got the following query:

    WITH OrderedResults AS (SELECT some_table.*, ROW_NUMBER() OVER (ORDER BY some_field) as RowNumber FROM some_table)
    SELECT TOP 10 *
    FROM OrderedResults
    WHERE RowNumber > 10;

    which works well for returning "paginated" recordsets. But when it comes to displaying "page" links and next and previous links, I need a total count of records found... something along the lines of the MySQL CALC_FOUND_ROWS feature...

    Is there some built-in MSSQL feature I can use for this, or do I have to do a SELECT count(*) FROM some_table to get this data?

    Any advice is appreciated
     
    tin2mon, Oct 16, 2010 IP
  2. sahanz

    sahanz Peon

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I think you need to count it separately, since your limiting records u have no option.
     
    sahanz, Oct 17, 2010 IP