How to implement LIMIT with Microsoft SQL Server?

Question

I have this query with mysql :

select * from table1 LIMIT 10,20

How can I do this with Microsoft sql ?

Answer 1

SELECT TOP 10 * FROM table;

Is the same as

SELECT * FROM table LIMIT 0,10;

Here's an article about implementing Limit in MsSQL Its a nice read, specially the comments.

Answer 2

Clunky, but it'll work.

SELECT TOP 10 * FROM table WHERE id NOT IN (SELECT TOP 10 id FROM table ORDER BY id) FROM table ORDER BY id

MSSQL's omission of a LIMIT clause is criminal, IMO. You shouldn't have to do this kind of kludgy workaround.

Answer 3

Starting SQL SERVER 2005, you can do this...

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 10 AND 20;

or something like this for 2000 and below versions...

SELECT TOP 10 * FROM (SELECT TOP 20 FROM Table ORDER BY Id) ORDER BY Id DESC

Answer 4

If i remember correctly (it's been a while since i dabbed with SQL Server) you may be able to use something like this: (2005 and up)

SELECT
    *
   ,ROW_NUMBER() OVER(ORDER BY SomeFields) AS [RowNum]
FROM SomeTable
WHERE RowNum BETWEEN 10 AND 20

Answer 5

This is almost a duplicate of a question I asked in October: http://stackoverflow.com/questions/216673/emulate-mysql-limit-clause-in-microsoft-sql-server-2000

If you're using Microsoft SQL Server 2000, there is no good solution. Most people have to resort to capturing the result of the query in a temporary table with a IDENTITY primary key. Then query against the primary key column using a BETWEEN condition.

If you're using Microsoft SQL Server 2005 or later, you have a ROW_NUMBER() function, so you can get the same result but avoid the temporary table.

SELECT t1.*
FROM (
    SELECT ROW_NUMBER OVER(ORDER BY id) AS row, t1.*
    FROM ( ...original SQL query... ) t1
) t2
WHERE t2.row BETWEEN @offset+1 AND @offset+@count;

You can also write this as a common table expression as shown in @Leon Tayson's answer.

Answer 6

SELECT *
FROM   (
       SELECT TOP 20
              t.*, ROW_NUMBER() OVER (ORDER BY field1) AS rn
       FROM   table1 t
       ) to
WHERE rn > 10

Answer 7

limit 10, 20 is not standard SQL

Answer 8

This is a multi step approach that will work in SQL2000.

-- Create a temp table to hold the data
CREATE TABLE #foo(rowID int identity(1, 1), myOtherColumns)

INSERT INTO #foo (myColumns) SELECT myData order By MyCriteria

Select * FROM #foo where rowID > 10

Answer 9

USE AdventureWorks;

GO

DECLARE @TOTALROWS int

WITH OrderedOrders AS

(

SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader 
SET @TOTALROWS = @@rowcount

)

SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 10 AND 20;

This is not working any help