SELECT SQL and upgrade your Performance - How to make LIMIT (from, to) in MSSQL 2005

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

Written by Stanislav Duben

Friday, 20 July 2007

Sample Image 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)

Quote this article on your site | Views: 11405 | Print

Comments (6)

1 Written by Joey, on 07-09-2007 17:07
In you example, what is row_number(), I have tried using 10 on replace row_number() and its not working.

You never declared row_number()

2 Written by Stanislav Duben , on 08-09-2007 10:37
Hi row_number() is new T-SQL command. This feature has syntax:
Code:

SELECT Row_name FROM (SELECT row_number() OVER (ORDER BY Column) as Row_name)

Another example:

Code:

SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row,
Description, Date FROM LOG) AS LogWithRowNumbers
WHERE Row >= 1 AND Row <=10

3 Written by arty, on 11-10-2007 20:23
very nice

4 Written by This e-mail address is being protected from spam bots, you need JavaScript enabled to view it , on 18-11-2007 08:46
This method doesn't support ordering over calculated fields! Any work around?

5 Written by This e-mail address is being protected from spam bots, you need JavaScript enabled to view it , on 02-12-2007 10:01
hi guys,
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_Name,Telephone,Mobile,User_Active FROM [User]) AS A WHERE A.rownum BETWEEN (0+1) AND (0 + 20)

Ciao,
Martin

6 Written by ALien, on 13-12-2007 14:24
Jup ;) thx :)
Poland thx

Write Comment

Please keep the topic of messages relevant to the subject of the article.
Personal verbal attacks will be deleted.
Please don't use comments to plug your web site. Such material will be removed.
Just ensure to *Refresh* your browser for a new security code to be displayed prior to clicking on the 'Send' button.
Keep in mind that the above process only applies if you simply entered the wrong security code.

Name:

E-mail

Homepage

BBCode:

Comment:

MathGuard security question, please solve:


F2T L E K X M QP1 IPN TYH IGQ L D 6 X6J B98 Y

Tag it:

< Prev

[ Back ]

Latest News

Popular

Main Menu

Syndicate