CODEWITHSUNDEEP
sqlsql-server
Iwnow
Iwnow

Reputation: 153

SQL Server query for paging

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.

Upvotes: 0

Views: 4399

Answers (4)

Alex Kudryashev
Alex Kudryashev

Reputation: 9480

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

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15185

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.

Upvotes: 1

Dan Abend
Dan Abend

Reputation: 116

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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.

Upvotes: 3

Related Questions