How to do paging in SQLServer 2008 R2? [duplicate]

Navigation
3

I tested it in two ways, but I did not succeed. 

select * from table OFFSET 10 ROWS

select top 10 * from table OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

Strangely enough I've never needed to use offset in this version of SQLServer, and now I'm having trouble with something relatively simple.

  

Errors displayed on console:

     

Message 102, Level 15, State 1, Line 3
   Incorrect syntax near   'OFFSET'.
  Message 153, Level 15, State 2, Line 3
  Invalid   usage of the NEXT option in the FETCH statement.

I may be wrong, but I confess that I did not find answers on the site that really answered my question (which is curious, since it is relatively simple).

    
asked by anonymous 22.08.2017 / 21:09

2 answers

1

There are two ways to do this. Let's assume that the query you will be doing is this: 

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In this case, you should determine the total results using: 

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

... may be inappropriate considering the efficiency of the result.

Now, to be able to make a more appropriate query, the following template could be the most efficient: 

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Here we will return 1-19 rows from the original query. Now just adapt to your code.

    
22.08.2017 / 23:58
0

Thiago, To page an ORDER BY ) of the records for your case: 

SELECT * FROM table 
ORDER BY 1 --Nome campo
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY
    
22.08.2017 / 21:19
Upload with ASP.NET Core json vs formdata Count elements of a column