0

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?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
RedsDevils
  • 1,413
  • 9
  • 26
  • 47

2 Answers2

3

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.

Pavel Radzivilovsky
  • 18,794
  • 5
  • 57
  • 67
2
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
LukeH
  • 263,068
  • 57
  • 365
  • 409