The TOP clause is used to limit the number of rows returned by a query.

Basic syntax:

SELECT TOP (n) column1, column2, ...
FROM table_name
[ORDER BY column_name [ASC|DESC]];
  • n → The number of rows you want to return.
  • ORDER BY → Optional but commonly used to control which rows are returned first.
  • TOP is available in all modern SQL Server versions.

Step 1: Example table

Assume the Employees table:

EmployeeID FirstName LastName Department Salary
1 John Doe IT 5000
2 Jane Smith HR 4500
3 Mike Brown IT 6000
4 Sara White HR 4700
5 Tom Green IT 5200

Step 2: Retrieve the top N rows

Example: Top 3 employees (any order):

SELECT TOP 3 *
FROM Employees;

Result:
The first 3 rows returned (order depends on physical storage, unless ORDER BY is used).

Step 3: Top N rows with ORDER BY

To control which rows are considered top, always use ORDER BY.

Example: Top 3 highest salaries:

SELECT TOP 3 FirstName, Salary
FROM Employees
ORDER BY Salary DESC;

Result:

FirstName Salary
Mike 6000
Tom 5200
John 5000

Step 4: Top N rows with a percentage

You can also get a percentage of rows using PERCENT:

SELECT TOP 40 PERCENT FirstName, Salary
FROM Employees
ORDER BY Salary DESC;

Explanation:

  • 40% of 5 rows → 2 rows returned (rounded).
  • Sort order is respected via ORDER BY.

Step 5: Top N rows with WITH TIES

WITH TIES returns all rows that tie with the last row in the top N based on the ORDER BY column.

SELECT TOP 2 WITH TIES FirstName, Salary
FROM Employees
ORDER BY Salary DESC;

Result:

FirstName Salary
Mike 6000
Tom 5200
John 5000

If two or more rows have the same salary as the last included row, all are returned.

Categorized in:

SQL Server,