How to Implement SQL Server's Equivalent of MySQL's LIMIT Clause?

Achieving LIMIT Functionality in SQL Server

SQL Server offers several ways to restrict the number of rows returned by a query, mirroring MySQL's LIMIT clause.

SQL Server 2005 and Subsequent Versions

From SQL Server 2005 onwards, the ROW_NUMBER() function within a Common Table Expression (CTE) provides a clean solution. Here's an illustration:

<code class="language-sql">USE AdventureWorks;
GO
WITH NumberedOrders AS (
    SELECT SalesOrderID, OrderDate,
           ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNum
    FROM Sales.SalesOrderHeader
)
SELECT *
FROM NumberedOrders
WHERE RowNum BETWEEN 10 AND 20;</code>

Older SQL Server Versions (2000 and Earlier)

For versions preceding SQL Server 2005, a combined approach using TOP and ORDER BY is necessary:

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

This technique first retrieves the top 20 rows, then selects the top 10 from that subset, effectively achieving a range-based limitation. Note that the nested ORDER BY is crucial for correct results. Replace YourTable and Id with your actual table and ordering column.

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