Iwnow Iwnow - 5 months ago 29
SQL Question

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.

lrb lrb
Answer

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.