SQL TOP

What is the SQL TOP clause?

Returns only the first N rows from a 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 TOP: Microsoft SQL Server, Azure SQL Database, Sybase ASE, MS Access (as TOP n). Not supported natively in PostgreSQL, MySQL, MariaDB, Oracle, SQLite.

SQL TOP Full Explanation

TOP is a proprietary SQL Server and Sybase extension that restricts a SELECT statement to the first N rows returned by the query processor. It is evaluated after the WHERE clause and before the ORDER BY is applied for ties. Because it executes on the server, performance is superior to client-side truncation. TOP may also work with the PERCENT modifier to return a percentage of rows and WITH TIES to include additional rows that match the last ordered value, ensuring deterministic results when duplicates exist. Unlike ANSI-standard LIMIT/OFFSET or FETCH FIRST, TOP is not portable across most databases, so migration requires syntax conversion. Without an ORDER BY clause, the rows returned are nondeterministic.

SQL TOP Syntax

SELECT TOP (expression) [PERCENT] [WITH TIES]
       column_list
FROM   table_name
[WHERE  conditions]
[ORDER BY sort_expression];

SQL TOP Parameters

expression (integer or subquery) - Number of rows (or percent) to return.
PERCENT (keyword) - Interprets expression as a percentage of the total rows.
WITH TIES (keyword) - Adds additional rows that have the same ORDER BY values as the last row within the TOP limit.

Example Queries Using SQL TOP

--Return the 10 highest-priced products
SELECT TOP (10) *
FROM   products
ORDER BY price DESC;

--Return the top 5 percent of sales orders by total amount
SELECT TOP (5) PERCENT order_id, total
FROM   sales_orders
ORDER BY total DESC;

--Return the first 3 rows but include ties when amounts are equal
SELECT TOP (3) WITH TIES order_id, total
FROM   sales_orders
ORDER BY total DESC;

Expected Output Using SQL TOP

Only the specified number of rows (or percent) are sent to the client
WITH TIES may increase the final row count if duplicate ORDER BY values exist

Use Cases with SQL TOP

Paginating large result sets on SQL Server applications.
Returning sample data quickly during development.
Getting the highest or lowest N values based on ORDER BY.
Limiting rows for reports or dashboard widgets where only a preview is needed.

Common Mistakes with SQL TOP

Omitting ORDER BY and assuming consistent ordering.
Expecting TOP to work in MySQL or PostgreSQL (use LIMIT instead).
Forgetting WITH TIES when duplicate ORDER BY values should be included.
Using TOP with UPDATE or DELETE without a deterministic ORDER BY, leading to unpredictable rows being modified.

First Introduced In

Microsoft SQL Server 7.0

Frequently Asked Questions

What does SQL TOP do?

TOP limits the number of rows returned by a SELECT query, reducing result size and improving performance.

Is SQL TOP part of the SQL standard?

No. TOP is proprietary to SQL Server and Sybase. Standard SQL uses FETCH FIRST or LIMIT.

How do I return a percentage of rows?

Add the PERCENT keyword: `SELECT TOP (5) PERCENT * FROM table;` returns 5 percent of all rows.

How can I keep duplicate ORDER BY values when using TOP?

Add WITH TIES after TOP to include any extra rows that share the same ORDER BY values as the final row within the specified limit.