How to make LIMIT (from, to) in MSSQL 2005

Sample ImageLots of developers complain, that Microsoft SQL hasn't something similar to LIMIT(from, to) as MySQL has. In past time there was a solution by SELECT TOP n and inserted SELECT TOP n- With new T-SQL commands in Miscrosoft SQL Server 2005  is situation simpler.

You can use this code, define @start as limit value From and @rowsperpage as number of records you need.

DECLARE @rowsperpage INT
SET @start = 120000 SET @rowsperpage = 10
SELECT * FROM ( SELECT row_number() OVER (ORDER BY column) AS rownum, column2, column3, .... columnX
FROM table
) AS A
WHERE A.rownum
BETWEEN (@start) AND (@start + @rowsperpage)

Add comment

Security code