SQL SELECT TOP

What is SQL SELECT TOP used for?

Returns only the first N rows from a query result set.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SELECT TOP: Supported: SQL Server (all versions), Azure SQL Database, Sybase ASE, MS Access. Not supported in PostgreSQL, MySQL, Oracle, SQLite (use LIMIT or FETCH FIRST instead).

SQL SELECT TOP Full Explanation

SELECT TOP is a proprietary, non-standard extension used mainly in Microsoft SQL Server and Sybase ASE to restrict the number (or percentage) of rows returned by a SELECT statement. When placed immediately after SELECT, TOP evaluates before ORDER BY, so you almost always combine it with ORDER BY to get deterministic results. The clause can also be paired with PERCENT to return a percentage of the result set and WITH TIES to include additional rows that match the last row in the ordering, ensuring no data is omitted when duplicates exist.Because TOP is parsed before ORDER BY, scalar expressions and variables in TOP cannot reference aliases defined later in the SELECT list. Unlike LIMIT or FETCH FIRST, TOP does not support an OFFSET component; for paging you combine TOP with an anti-join or use OFFSET FETCH in modern SQL Server versions. TOP applies only to the final result set after all joins, filters, GROUP BY, and HAVING clauses are processed.

SQL SELECT TOP Syntax

SELECT TOP (quantity | @var)
       [PERCENT]
       [WITH TIES]
       select_list
FROM   table_name
[WHERE conditions]
[ORDER BY column_list];

SQL SELECT TOP Parameters

- quantity (int or bigint) - The exact number of rows to return.
- @var (int or bigint variable) - T-SQL variable supplying the row count.
- PERCENT (keyword) - Interprets quantity as a percentage of the result set instead of an absolute count.
- WITH TIES (keyword) - Includes additional rows that match the ordering value of the last selected row; requires ORDER BY.
- select_list - Columns or expressions to retrieve.
- table_name - Target table or joined tables.
- conditions - Optional filter predicate.
- column_list - Columns that define sort order (strongly recommended).

Example Queries Using SQL SELECT TOP

-- Return the 5 highest-priced products
SELECT TOP 5 *
FROM   Products
ORDER BY UnitPrice DESC;

-- Return the top 10 percent of customers by revenue, include ties
SELECT TOP 10 PERCENT WITH TIES CustomerID, SUM(TotalDue) AS revenue
FROM   Sales
GROUP BY CustomerID
ORDER BY revenue DESC;

-- Use a variable for dynamic limit
DECLARE @n INT = 3;
SELECT TOP (@n) name, created_at
FROM   Users
ORDER BY created_at DESC;

Expected Output Using SQL SELECT TOP

The result set contains only the specified number or percentage of rows
WITH TIES may increase the row count beyond the stated limit if multiple rows share the same ordering value as the last row

Use Cases with SQL SELECT TOP

Fetch a sample of rows for quick inspection without scanning full tables
Display the most recent N records in dashboards or logs
Implement simple server-side pagination (first page)
Pull top-selling products, highest-spending customers, or other ranked lists
Limit data returned to UI layers for performance and bandwidth savings

Common Mistakes with SQL SELECT TOP

Omitting ORDER BY, resulting in non-deterministic row selection
Expecting TOP to support OFFSET for paging; use OFFSET FETCH instead
Forgetting WITH TIES when duplicate ordering values exist, causing inconsistent totals
Using aliases from the SELECT list inside the ORDER BY when also referencing them in TOP expressions (parse order issue)

First Introduced In

Microsoft SQL Server 7.0

Frequently Asked Questions

How does SELECT TOP differ from LIMIT?

LIMIT is used in MySQL and PostgreSQL and appears after ORDER BY. SELECT TOP is placed right after SELECT and lacks built-in OFFSET support.

Why should I always use ORDER BY with TOP?

Without ORDER BY, SQL Server chooses arbitrary rows, which can change between executions. ORDER BY guarantees deterministic, predictable results.

Can I parameterize the row count in TOP?

Yes. Enclose the variable in parentheses: `SELECT TOP (@rows) * FROM MyTable;` The variable must be an integer data type.

How do I return the next page after TOP 10?

Use OFFSET FETCH: `ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;` or wrap the query with ROW_NUMBER() and filter by row number range.