|
How to make LIMIT (from, to) in MSSQL 2005 |
|
|
|
Written by Stanislav Duben
|
|
Friday, 20 July 2007 |
|
Lots 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
DECLARE @start 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)
|
Comments
Poland thx
nice query, I tried and it works but...
at my first page I get 20 records and all the following pages 21.
so I made a little adjustment, (I added +1 @start) Would like to here if it is oke, or I am doing something wrong
Debug: SELECT * FROM (SELECT row_number() OVER (ORDER BY User_Name) AS rownum, User_ID,User_Na me,Telephone,Mo bile,User_Activ e FROM [User]) AS A WHERE A.rownum BETWEEN (0+1) AND (0 + 20)
Ciao,
Martin
Code:
SELECT Row_name FROM (SELECT row_number() OVER (ORDER BY Column) as Row_name)<br />Another example:
Code:
SELECT Description, Date<br /> FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row,<br /> Description, Date FROM LOG) AS LogWithRowNumbers<br /> WHERE Row >= 1 AND Row <=10You never declared row_number()
RSS feed for comments to this post.