I'm retrieving thousands data from database. I would like to show on web pages by limit of 100 records. I can show First 100 record with the following SQL:
SELECT TOP 100 * FROM TBLStock
And How can I navigate next r开发者_如何学Pythonecords 101 to 200, 201 to 300, etc.. ? Please Help me. I am using SQL Server 2005. HELP! How can I write in SQL Server like LIMIT in MySQL?
Method 1: SELECT TOP 100 FROM (SELECT TOP 500 ORDER BY )
AFAIK this is the only way before Yukon (MS SQL server 2005). Double select with reverse ordering in the middle.
Surprisingly, it is also said to be efficient.
Method 2: Use Row_Number() function that is available starting SQL Server 2005, as the other post suggests.
SELECT t.*
FROM
(
SELECT first_column, second_column, third_column, etc,
ROW_NUMBER() OVER (ORDER BY sort_column) AS row_num
FROM your_table
) AS t
WHERE t.row_num BETWEEN 50 AND 100
ORDER BY t.row_num
精彩评论