SQL Topics
TOP Clause
title: TOP Clause
In large databases, retrieving every row is often unnecessary. Most applications only need a small portion of data such as the top-selling products, highest-paid employees, latest orders, or best-performing students.
Returning millions of rows when only a few records are needed can negatively affect performance and increase resource consumption.
To solve this problem, some database systems provide the TOP Clause.
The TOP clause limits the number of rows returned by a query. It is most commonly used in Microsoft SQL Server and Microsoft Access.
Although TOP serves a purpose similar to the LIMIT clause, the syntax differs depending on the database system.
TOP is frequently used in reporting, analytics, dashboards, ranking systems, and performance optimization.
What is the TOP Clause?
The TOP clause limits the number of rows returned by a query result.
Example:
SELECT TOP 5 *
FROM Students;Result:
Only the first 5 rows are returned.
Even if the table contains thousands of records, SQL returns only the specified number.
Why is TOP Important?
Consider an Employees table:
500,000 EmployeesWithout TOP:
SELECT *
FROM Employees;Every employee record is returned.
Problems:
- Slow query execution
- Increased memory usage
- Higher network traffic
- Poor application performance
With TOP:
SELECT TOP 10 *
FROM Employees;Only ten records are returned.
Benefits:
- Faster execution
- Reduced server load
- Better user experience
- Improved reporting performance
Basic TOP Syntax
The general syntax is:
SELECT TOP Number
ColumnName
FROM TableName;Example:
SELECT TOP 3 *
FROM Students;Only three rows are returned.
Understanding the Syntax
Example:
SELECT TOP 3 *
FROM Students;SELECT
Retrieves data.
TOP
Limits the number of rows.
3
Maximum rows to return.
*
Selects all columns.
FROM
Specifies the source table.
Creating a Sample Table
Example:
CREATE TABLE Students (
StudentID INT,
Name VARCHAR(100),
Marks INT
);Insert records:
INSERT INTO Students VALUES
(1, 'Rahul', 85),
(2, 'Priya', 92),
(3, 'Amit', 78),
(4, 'Neha', 95),
(5, 'Rohan', 88);Current data:
| StudentID | Name | Marks |
|---|---|---|
| 1 | Rahul | 85 |
| 2 | Priya | 92 |
| 3 | Amit | 78 |
| 4 | Neha | 95 |
| 5 | Rohan | 88 |
Retrieving Top Records
Query:
SELECT TOP 2 *
FROM Students;Result:
| StudentID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
Only the first two rows are returned.
TOP with ORDER BY
TOP becomes much more useful when combined with ORDER BY.
Example:
SELECT TOP 3 *
FROM Students
ORDER BY Marks DESC;Process:
Sort Students by Marks
↓
Return Top 3 RowsResult:
| Name | Marks |
|---|---|
| Neha | 95 |
| Priya | 92 |
| Rohan | 88 |
These are the highest-scoring students.
Finding Highest-Paid Employees
Example:
SELECT TOP 5 *
FROM Employees
ORDER BY Salary DESC;Result:
The five highest-paid employees are returned.
This is a common reporting requirement.
Finding Lowest Values
Example:
SELECT TOP 3 *
FROM Products
ORDER BY Price ASC;Result:
The three cheapest products are displayed.
TOP with WHERE
TOP can be combined with filtering.
Example:
SELECT TOP 5 *
FROM Employees
WHERE Department = 'IT';Process:
Filter IT Employees
↓
Return First 5 RecordsOnly matching records are considered.
TOP with Multiple Conditions
Example:
SELECT TOP 3 *
FROM Employees
WHERE Department = 'IT'
ORDER BY Salary DESC;Result:
The three highest-paid employees in the IT department.
TOP PERCENT
SQL Server supports percentage-based results.
Example:
SELECT TOP 20 PERCENT *
FROM Employees;Result:
Returns approximately 20% of all rows.
If:
100 Employeesexist,
20 Employeesare returned.
TOP WITH TIES
Sometimes multiple rows share the same value.
Example:
SELECT TOP 3 WITH TIES *
FROM Students
ORDER BY Marks DESC;If the third and fourth students have identical marks:
92
92both rows are returned.
This ensures fair ranking.
TOP vs LIMIT
Many database systems use LIMIT instead of TOP.
SQL Server
SELECT TOP 5 *
FROM Students;MySQL
SELECT *
FROM Students
LIMIT 5;PostgreSQL
SELECT *
FROM Students
LIMIT 5;SQLite
SELECT *
FROM Students
LIMIT 5;Comparison:
| Feature | TOP | LIMIT |
|---|---|---|
| SQL Server | Yes | No |
| MySQL | No | Yes |
| PostgreSQL | No | Yes |
| SQLite | No | Yes |
| Oracle | No | Uses FETCH |
TOP vs FETCH FIRST
Oracle and modern SQL standards use FETCH.
Example:
SELECT *
FROM Students
FETCH FIRST 5 ROWS ONLY;Equivalent SQL Server query:
SELECT TOP 5 *
FROM Students;Real-World Example
Imagine an online shopping platform.
Products table:
| ProductID | ProductName | Sales |
|---|---|---|
| 1 | Laptop | 500 |
| 2 | Mobile | 700 |
| 3 | Mouse | 150 |
Management wants the top-selling products.
Query:
SELECT TOP 2 *
FROM Products
ORDER BY Sales DESC;Result:
| ProductName | Sales |
|---|---|
| Mobile | 700 |
| Laptop | 500 |
Only the best-performing products are shown.
Common Errors
Using TOP Without ORDER BY
Example:
SELECT TOP 5 *
FROM Students;The returned rows may not be predictable.
Use:
ORDER BYwhen a specific order is required.
Expecting TOP to Sort Data
Wrong assumption:
TOP automatically sorts data.Reality:
TOP only limits rows.Sorting requires ORDER BY.
Using TOP in MySQL
Wrong:
SELECT TOP 5 *
FROM Students;MySQL uses:
LIMIT 5instead.
Using Negative Values
Wrong:
SELECT TOP -5 *
FROM Students;Invalid syntax.
Best Practices
Always Combine TOP with ORDER BY
Good:
SELECT TOP 10 *
FROM Products
ORDER BY Sales DESC;Retrieve Only Required Columns
Good:
SELECT TOP 5
Name,
Salary
FROM Employees;Avoid unnecessary data retrieval.
Use TOP for Reports
TOP is ideal for:
- Top-selling products
- Highest salaries
- Best-performing students
- Latest transactions
Consider Pagination
For large datasets, pagination may be more appropriate.
Common Interview Questions
What is the purpose of TOP?
TOP limits the number of rows returned by a query.
Which database system commonly uses TOP?
Microsoft SQL Server.
What is the difference between TOP and LIMIT?
Both limit rows, but TOP is primarily used in SQL Server while LIMIT is used in MySQL, PostgreSQL, and SQLite.
Why should TOP usually be used with ORDER BY?
ORDER BY ensures that the returned rows are meaningful and predictable.
Summary
The TOP clause is an important SQL feature used to restrict the number of rows returned by a query. It helps improve performance, supports reporting requirements, and allows users to focus on the most relevant records.
In this lesson, you learned:
- What TOP is
- Why TOP is important
- Basic syntax
- TOP with ORDER BY
- TOP with WHERE
- TOP PERCENT
- TOP WITH TIES
- TOP vs LIMIT
- TOP vs FETCH
- Best practices
Understanding TOP is valuable because modern applications frequently need only a small subset of data rather than entire datasets.
Next Step
Continue to the next lesson:
BETWEEN Operator →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for TOP Clause.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, filtering, top
Related SQL Topics