How to Implement the Equivalent of a LIMIT Clause in SQL Server?

Implement the equivalent function of LIMIT clause in SQL Server

Many SQL databases support the LIMIT clause to limit the number of rows returned in the result set. Although MySQL supports LIMIT directly, SQL Server has no equivalent clause. However, SQL Server provides multiple ways to achieve the same functionality.

Method 1: Use ROW_NUMBER() and BETWEEN

For SQL Server 2005 and above, you can use the ROW_NUMBER() function to assign a row number to each record in the result set. Then, use the BETWEEN operator to filter the result set based on row numbers.

<code class="language-sql">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;</code>

Method 2: Use TOP and outer query

For SQL Server 2000 and earlier versions, you can use nested queries, combining TOP and ORDER BY clauses. The outer query selects a specified number of rows from the inner query.

<code class="language-sql">SELECT TOP 10 * FROM (SELECT TOP 20 * FROM Table ORDER BY Id) ORDER BY Id DESC</code>

The above is the detailed content of How to Implement the Equivalent of a LIMIT Clause in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!