How can I make MySQL's LIMIT in SQL Server?

Question

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 records 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?

Answer 1

Check out this post on paging in SQL Server: http://stackoverflow.com/questions/2840/paging-sql-server-2005-results

Answer 2

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.

Answer 3

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