How to implement LIMIT with SQL Server? [duplicate]

Solution 1:

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

Solution 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.

Solution 3:

Starting with SQL SERVER 2012, you can use the OFFSET FETCH Clause:

USE AdventureWorks;
GO
SELECT SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader 
ORDER BY SalesOrderID
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;
GO

http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx

This may not work correctly when the order by is not unique.

If the query is modified to ORDER BY OrderDate, the result set returned is not as expected.

Solution 4:

This is how I limit the results in MS SQL Server 2012:

SELECT * 
FROM table1
ORDER BY columnName
  OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

NOTE: OFFSET can only be used with or in tandem to ORDER BY.

To explain the code line OFFSET xx ROWS FETCH NEXT yy ROW ONLY

The xx is the record/row number you want to start pulling from in the table, i.e: If there are 40 records in table 1, the code above will start pulling from row 10.

The yy is the number of records/rows you want to pull from the table.

To build on the previous example: If table 1 has 40 records and you began pulling from row 10 and grab the NEXT set of 10 (yy). That would mean, the code above will pull the records from table 1 starting at row 10 and ending at 20. Thus pulling rows 10 - 20.

Check out the link for more info on OFFSET

Solution 5:

This is almost a duplicate of a question I asked in October: 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.