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)

Tag it:

Comments

0 #13 Nilesh Patel 2011-12-20 08:04

Thanks

Quote

0 #12 ranjith 2011-08-20 01:48

Thanks :)

Quote

0 #11 Stanislav Duben 2011-08-18 22:17

Quoting ViDom:

How to???????????:
SELECT [title],SUM([co unt]) AS count
FROM [test].[dbo].[posts]
GROUP BY [title]
ORDER BY [title];

I have not ID column )))

I don't understand your question. In example we are not using ID column. Your example should work, so try explain more what you are looking for.

Quote

0 #10 ViDom 2011-08-14 11:14

How to???????????:
SELECT [title],SUM([co unt]) AS count
FROM [test].[dbo].[posts]
GROUP BY [title]
ORDER BY [title];

I have not ID column )))

Quote

+2 #9 anon 2011-03-24 04:07

MS SQL

PGSQL and MySQL

Quote

+1 #8 Han Mai 2011-02-24 09:10

Quoting Joey:

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()

ROW_NUMBER() is a function in SQL SERVER 2K5 or higher.

Quote

+1 #7 Magnus 2010-07-15 13:35

Thanks for your Tipp. Great idea.

Quote

0 #6 ALien 2007-12-13 16:24

Jup

thx

Poland thx

Quote

+1 #5 martijn 2007-12-02 12: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_Na me,Telephone,Mo bile,User_Activ e FROM [User]) AS A WHERE A.rownum BETWEEN (0+1) AND (0 + 20)

Ciao,
Martin

Quote