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

Replicating MySQL's LIMIT in SQL Server

MySQL's LIMIT clause restricts the number of rows returned. SQL Server achieves this using the TOP clause. Here's how to get the same functionality:

SQL Server 2005 and Later

For SQL Server 2005 and subsequent versions, a common approach involves using ROW_NUMBER():

<code class="language-sql">SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Id) as RowNum
    FROM OrderedOrders
) as RankedOrders
WHERE RowNum BETWEEN 10 AND 20;</code>

This assigns a unique rank to each row and then filters based on that rank.

SQL Server 2000 and Earlier

In older versions (SQL Server 2000 and below), a nested SELECT statement is necessary:

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

This first selects the top 20 rows, then from that subset, selects the top 10. Note the importance of the secondary ORDER BY clause for consistent results. The ordering in both queries is crucial for obtaining the desired rows.

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