Pagination query for mssql server 2008 Throwing In

问题:

I am Working on pagination in jsp(and i am new to writing sql).

I done my research and found simple queries from

pagination in SQL server 2008 and How to do pagination in SQL Server 2008

SELECT * FROM document  ORDER BY id OFFSET
                    "+iPageNo+"  ROWS FETCH NEXT 10 ROWS ONLY;

in the above query ipageNo is coming 0 for the first time(even i tried by putting 1 manually)

Still giving the error Incorrect syntax near 'OFFSET'.

What i missed here ? Thanks in advance.

回答1:

You will note from ORDER BY Clause (Transact-SQL) this syntax is not supported in SQL Server 2008.

You can see from the 2008 documentation

**Syntax**

[ ORDER BY 
    {
    order_by_expression 
  [ COLLATE collation_name ] 
  [ ASC | DESC ] 
    } [ ,...n ] 
]

where as the 2012 documentation

**Syntax**
ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[ <offset_fetch> ]


<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}

Maybe also have a look at how to do pagination in sql server 2008

回答2:

Here is my work around and working fine now.

SELECT * FROM   (SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownumber,*
        FROM document)  as somex  WHERE  rownumber >= (1+1)*10-9
                         AND rownumber <=(1+1)*10

In the above query i am replacing (1+1) with (pageNUmber+1).

Please feel free to suggest me if any elegant way available.

回答3:

I hope this will help (works on SQL Server 2008).

回答4:

DECLARE @Page int
SET @Page = 2
DECLARE @Amount int
SET @Amount = 25
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY group_id) AS rownumber, * FROM table_name 
WHERE Column LIKE '%Search_Value%') as somex  
WHERE  rownumber >= (@Page+1)* @Amount-(@Amount - 1) AND rownumber <= (@Page+1) * @Amount

I took it one step further. Added some variables to make inputting the information a little better. At my company we have older databases so this feed helped me a lot.