Pagination query for mssql server 2008 Throwing Incorrect syntax near 'OFFSET'

Question

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.

score 5 · Answer 1 · edited May 23 '17 at 12:33

5

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

edited May 23 '17 at 12:33

Community

1
1

answered Mar 22 '13 at 06:41

Adriaan Stander

162,879
31
289
284

thanks for the info.But interesting point is ORDER BY is working for me. – Suresh Atta Mar 22 '13 at 07:31
1

Yes, the order by will work fine, it is the pagination section that is only available after SQL SERVER 2008 – Adriaan Stander Mar 22 '13 at 07:32
Ahh..after some workaround i found some query and posted here.Can you please check it once if possible? – Suresh Atta Mar 22 '13 at 07:47

score 3 · Accepted Answer · answered Mar 22 '13 at 07:45

3

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).

enter image description here

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

answered Mar 22 '13 at 07:45

Suresh Atta

120,458
37
198
307

Hi how can we add order by to the query? I tried. It gave me an error saying "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified." Is it possible to have aggregate functions like "Group By and Order By" ? – yohan.jayarathna Feb 24 '14 at 14:35

score 1 · Answer 3 · answered Mar 25 '13 at 16:45

1

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

answered Mar 25 '13 at 16:45

msi77

1,602
1
11
10

+1 .Thanks for the link @msi.i will try that.have alook on my answer also. – Suresh Atta Mar 25 '13 at 16:49

score 0 · Answer 4 · answered Oct 24 '16 at 17:20

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.

Pagination query for mssql server 2008 Throwing Incorrect syntax near 'OFFSET'

4 Answers4

Linked