Table of Contents

Introduction

SQL Server SELECT TOP Statement clause limits the number of rows returned by a query. It is useful when you need a small result set, such as the five highest-priced products, the newest orders, or a sample of records from a large table. You can also use TOP with a percentage, include rows tied at the limit, and pass a variable to control the number of rows.

To select a meaningful set of top or bottom records, combine TOP with ORDER BY. Without an explicit sort order, SQL Server does not guarantee which qualifying rows will be returned.

SQL Server SELECT TOP Syntax

The basic syntax places TOP immediately after the SELECT keyword:

SELECT TOP (expression) [PERCENT] [WITH TIES]
    column1, column2, ...
FROM schema_name.table_name
[WHERE condition]
[ORDER BY sort_expression [ASC | DESC]];
  • expression specifies the number of rows to return. It can be a numeric expression or a variable.
  • PERCENT tells SQL Server to return a percentage of the qualifying rows instead of a fixed count.
  • WITH TIES includes additional rows that have the same ORDER BY values as the last row in the TOP limit.
  • ORDER BY defines which rows are considered first. It is optional in syntax but important when you need a particular top-N result.

Select the First N Rows with TOP

Use a numeric value in parentheses to return a fixed number of rows. This example retrieves five records from the Products table:

SELECT TOP (5) ProductID, ProductName, UnitPrice
FROM dbo.Products;

The query returns five qualifying rows, but it does not specify which five. SQL Server can choose any five rows that satisfy the query because no ordering has been requested. The result order may change when the execution plan, indexes, or underlying data changes.

Use TOP with ORDER BY

To retrieve the five most expensive products, sort by price in descending order before limiting the result:

SELECT TOP (5) ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY UnitPrice DESC;

For the five least expensive products, use ascending order:

SELECT TOP (5) ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY UnitPrice ASC;

If the sort column contains duplicate values, the order among tied rows is not fully deterministic. Add a unique column as a secondary sort key when you need repeatable results:

SELECT TOP (5) ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY UnitPrice DESC, ProductID ASC;

Here, price determines the main order and ProductID breaks ties.

Use TOP PERCENT

The PERCENT option returns a percentage of the rows that qualify for the query. It is useful when the desired result size should scale with the number of matching records.

SELECT TOP (10) PERCENT
       EmployeeID, FirstName, Salary
FROM dbo.Employees
ORDER BY Salary DESC;

This query returns the highest-paid ten percent of employees. SQL Server rounds a fractional row count up to the next whole row. The percentage is calculated from the rows qualifying for the query, after filtering has been applied.

Include Matching Rows with WITH TIES

Sometimes several rows share the same value at the boundary of a TOP result. By default, SQL Server returns only the requested number of rows, even if that means another row with the same sort value is excluded. Use WITH TIES to include all rows tied with the last row in the result.

SELECT TOP (3) WITH TIES
       ProductName, UnitPrice
FROM dbo.Products
ORDER BY UnitPrice DESC;

If the third row’s price is shared by one or more additional products, those products are included too. The result can therefore contain more than three rows.

WITH TIES requires an ORDER BY clause. The tie comparison uses the complete set of ORDER BY expressions, so adding a unique key to the sort list usually prevents otherwise equal prices from being treated as ties.

Use a Variable in the TOP Clause

You can use a local variable to make the row limit dynamic. Enclose the variable in parentheses after TOP:

DECLARE @RowLimit INT = 10;

SELECT TOP (@RowLimit)
       ProductID, ProductName, UnitPrice
FROM dbo.Products
ORDER BY ProductID;

This is useful in stored procedures where an application supplies the number of rows to retrieve. Validate input values in application code or in the procedure so that the requested limit is within the range your application supports.

Use TOP with a WHERE Clause

The WHERE clause filters rows before TOP limits the qualifying result. For example, to retrieve the ten most recent orders for a particular customer:

SELECT TOP (10)
       OrderID, CustomerID, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerID = 25
ORDER BY OrderDate DESC, OrderID DESC;

Only orders for customer 25 are considered. The result then contains up to ten rows, with the newest order dates first. The OrderID sort key provides a consistent tie-breaker when two orders have the same date.

Use TOP with DISTINCT

You can combine TOP and DISTINCT to limit the number of unique values returned. DISTINCT removes duplicate selected rows, and TOP limits the resulting set.

SELECT DISTINCT TOP (5) City
FROM dbo.Customers
ORDER BY City ASC;

This query returns up to five distinct city names in ascending order. The order of the SELECT modifiers can be written as shown; both DISTINCT and TOP apply to the query result rather than changing the source table.

Use TOP with GROUP BY and Aggregate Functions

TOP can limit grouped results. This example finds the five product categories with the largest number of products:

SELECT TOP (5)
       CategoryID,
       COUNT(*) AS ProductCount
FROM dbo.Products
GROUP BY CategoryID
ORDER BY COUNT(*) DESC, CategoryID ASC;

The rows are grouped by category, counted, and sorted by the aggregate value. TOP then returns the first five groups. A secondary sort by CategoryID makes the output predictable when two categories have the same count.

You can use the same pattern with aggregate functions such as SUM(), AVG(), and MAX(), depending on the metric you want to rank.

Use TOP in UPDATE Statements

SQL Server also supports TOP in data modification statements. For example, you can update a limited number of rows that meet a condition:

UPDATE TOP (10) dbo.Orders
SET IsReviewed = 1
WHERE IsReviewed = 0;

This updates up to ten qualifying rows. The statement does not let you specify which ten rows are chosen through an ORDER BY clause directly in the UPDATE syntax. If a specific ordered subset must be updated, identify the target keys in a subquery or common table expression with an appropriate ORDER BY and then update those rows. Use a transaction and verify the target set before running production updates.

Use TOP in DELETE Statements

You can also delete a limited number of qualifying rows with TOP:

DELETE TOP (100)
FROM dbo.LogEntries
WHERE CreatedAt < '2025-01-01';

This removes up to one hundred rows that match the condition. It does not guarantee that the oldest one hundred rows are deleted. To select a particular ordered batch, use a CTE or a subquery that identifies the keys with ORDER BY, then delete by joining to those keys. Review the rows first with a SELECT statement and use a transaction when appropriate.

TOP with a Common Table Expression

A common table expression (CTE) can make it easier to select an ordered subset and then use that subset in another query. For example, this query returns the three newest orders and their customer IDs:

WITH LatestOrders AS
(
    SELECT TOP (3)
           OrderID, CustomerID, OrderDate
    FROM dbo.Orders
    ORDER BY OrderDate DESC, OrderID DESC
)
SELECT OrderID, CustomerID, OrderDate
FROM LatestOrders
ORDER BY OrderDate DESC, OrderID DESC;

The ORDER BY inside the CTE determines which three rows TOP selects. The outer ORDER BY specifies the presentation order of the final result. Relational query results do not carry a guaranteed order from an inner query to the outer query.

TOP Compared with OFFSET and FETCH

Both TOP and OFFSET ... FETCH can limit the rows returned by a SELECT statement, but they are suited to different query patterns.

Feature TOP OFFSET / FETCH
Fixed row limit Supported Supported with FETCH
Skip a number of rows Not directly Supported with OFFSET
Requires ORDER BY No, but recommended for top-N selection Yes
Include boundary ties Supported with WITH TIES No equivalent WITH TIES option
Typical use Top-N queries and limited batches Page-based query results

For example, to return rows 11 through 20 in a stable order, use OFFSET and FETCH:

SELECT ProductID, ProductName
FROM dbo.Products
ORDER BY ProductID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

TOP is usually more direct when you only need the first N rows. OFFSET and FETCH are designed for skipping rows and retrieving a particular page.