SQL Server query for paging

Question

I got simple SQL Server query:

SELECT TOP 100 
    [ID], [SUMMARY], [NAME]   
FROM 
    [db_test].[dbschema].[dborders]

It works fine, and query 100 records from table, now for paging mechanism I need to get let's say 10 first records:

SELECT TOP 10 
FROM 
    (SELECT TOP 100 
         [ID], [SUMMARY], [NAME]   
     FROM 
         [db_test].[dbschema].[dborders])

but I get an error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.

Please tell me what I'm doing wrong, or give me example of correct statement.

use *; SELECT TOP 10 *; also paging can't be done with this approach; check [this](http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) — techspider, Jul 06 '16 at 18:38
You need a `*` after the `SELECT TOP 10` and *before* the `FROM` ! `SELECT TOP 10 * FROM ....` — marc_s, Jul 06 '16 at 18:46
Also, mind you: using `TOP` *without* explicitly specifying an `ORDER BY` clause is moot - you get the TOP 10 rows - but ordered by what?? Since you didn't specify anything, SQL Server will return just simply **10 arbitrary rows** in no particular order at all .... if you use `TOP`, you should **always** explicitly specify an `ORDER BY`, too ! — marc_s, Jul 06 '16 at 18:48

score 3 · Answer 1 · answered Jul 06 '16 at 18:47

Your query has two syntactic errors and one logical error:

Missing * (or list of columns) in the SELECT
Missing table alias for the subquery
No ORDER BY, which should be used with TOP

So:

SELECT TOP 10 o.*
FROM (SELECT TOP 100 [ID], [SUMMARY], [NAME]
      FROM [db_test].[dbschema].[dborders] o
      ORDER BY id
     ) o
ORDER BY id;

If you do not use ORDER BY, then SQL Server is not guaranteed to return the same results each time the query is run. It is important to have a stable sort order when you are doing paging.

score 3 · Answer 2 · answered Jul 06 '16 at 18:47

For SQL Server 2012 paging, check out the keywords OFFSET and FETCH.

OFFSET is the starting row. For the first page, OFFSET is zero. If you're showing 10 items per page, the second page would start with OFFSET 10.

FETCH is the number of rows to return just like when using TOP.

SELECT [ID], [SUMMARY], [NAME]
FROM [db_test].[dbschema].[dborders]
ORDER BY [ID]
OFFSET @OffsetRows ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

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

1

The syntax error is occurring because you are not aliasing the subquery. You have to name an object that you are selecting from.

SELECT TOP 10 * FROM (
    SELECT TOP 100 [ID] ,[SUMMARY] ,[NAME]   FROM [db_test].[dbschema].[dborders] A ORDER BY id
)AS B ORDER BY ID

Also, as techspider points out below, the first error you will get will be due to a missing column list and the second error you should get will be from missing subquery alias.

One other point is that there are more optimized ways to implement paging in stored procedures. However, this answers your original question about a syntax error.

edited May 23 '17 at 12:17

Community

1
1

answered Jul 06 '16 at 18:39

Ross Bush

14,648
2
32
55

1

You still have to use `TOP 10 *` along with alias name – techspider Jul 06 '16 at 18:41
1

Yes, there were two problems with the query. The missing alias and the column list. – Ross Bush Jul 06 '16 at 18:42
You beat me..i was going to answer the same...anyway +1 – Hackerman Jul 06 '16 at 18:43
@lrb, you have to edit your answer to address the second issue too; – techspider Jul 06 '16 at 18:44
Ok, I executed your query and I get:Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'FROM'. Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'AS'. – Iwnow Jul 06 '16 at 18:44
1

Without an order by on both queries this answer is not going to work well. If the order was added to both queries this would be an excellent answer. – Sean Lange Jul 06 '16 at 18:51
Updated with order by's – Ross Bush Jul 06 '16 at 18:54
@Gordon Linoff has already provided an excellent answer with explanation – techspider Jul 06 '16 at 18:54

score 0 · Answer 4 · answered Jul 06 '16 at 18:58

0

If your MS SQL Server is pre-2012 then you can use row_number() for pagination. This is a sample.

declare @startRow int=20, @rowsPerPage int = 10 --or receive as SP parameters

;with cte as (--start CTE
   SELECT [ID], [SUMMARY], [NAME],
      row_number() over(order by id) rn --enumerate rows
   FROM [db_test].[dbschema].[dborders]
)--end CTE
select [ID], [SUMMARY], [NAME]
from cte
where rn between @startRow and @startRow + @rowsPerPage

answered Jul 06 '16 at 18:58

Alex Kudryashev

9,120
3
27
36

pre-2012 and post 2005 (or was it 2008?) – Hogan Jul 06 '16 at 20:28
CTE support introduced in 2008. – Alex Kudryashev Jul 06 '16 at 20:31

SQL Server query for paging

4 Answers4

Linked