SQL TOP, LIMIT, FETCH FIRST, or ROWNUM Clause

Welcome to The Coding College, your trusted resource for learning coding and programming! In this tutorial, we’ll explore how to retrieve a specific number of rows using SQL clauses such as TOP, LIMIT, FETCH FIRST, or ROWNUM, based on the database system you are using.

Introduction

In SQL, you often need to limit the number of rows returned by a query. This is especially useful when working with large datasets where retrieving all rows can affect performance. Depending on your database system, the method to limit rows differs:

• TOP: Used in Microsoft SQL Server.
• LIMIT: Common in MySQL and PostgreSQL.
• FETCH FIRST: Used in SQL:2008-compliant databases (e.g., Oracle 12c+, DB2).
• ROWNUM: Used in earlier versions of Oracle databases.

SQL Syntax

Using TOP (SQL Server)

SELECT TOP number|percent column1, column2, ...  
FROM table_name  
WHERE condition;  

Using LIMIT (MySQL, PostgreSQL)

SELECT column1, column2, ...  
FROM table_name  
WHERE condition  
LIMIT number;  

Using FETCH FIRST (SQL:2008 Standard)

SELECT column1, column2, ...  
FROM table_name  
WHERE condition  
FETCH FIRST number ROWS ONLY;  

Using ROWNUM (Oracle)

SELECT column1, column2, ...  
FROM table_name  
WHERE ROWNUM <= number;  

Examples

Sample Table: students

Using the TOP Clause (SQL Server)

Query: Retrieve the top 2 highest-scoring students.

SELECT TOP 2 name, score  
FROM students  
ORDER BY score DESC;  

Result:

Using the LIMIT Clause (MySQL, PostgreSQL)

Query: Retrieve the top 3 youngest students.

SELECT name, age  
FROM students  
ORDER BY age ASC  
LIMIT 3;  

Result:

Using the FETCH FIRST Clause (SQL:2008 Standard)

Query: Retrieve the top 1 student by grade in alphabetical order.

SELECT name, grade  
FROM students  
ORDER BY grade ASC  
FETCH FIRST 1 ROWS ONLY;  

Result:

Using the ROWNUM Clause (Oracle)

Query: Retrieve the top 2 students with grades of B or higher.

SELECT name, grade  
FROM students  
WHERE grade IN ('A', 'B') AND ROWNUM <= 2;  

Result:

Combining LIMIT or FETCH FIRST with OFFSET

In some scenarios, you may need to paginate your data by retrieving rows after skipping a specific number of records.

Using LIMIT with OFFSET (MySQL, PostgreSQL)

SELECT name, score  
FROM students  
ORDER BY score DESC  
LIMIT 2 OFFSET 2;  

Result:

Using FETCH FIRST with OFFSET (SQL:2008 Standard)

SELECT name, age  
FROM students  
ORDER BY age ASC  
OFFSET 2 ROWS FETCH FIRST 2 ROWS ONLY;  

Key Differences Between Clauses

Common Use Cases

• Leaderboard Display:

SELECT TOP 10 name, score FROM students ORDER BY score DESC;  

• Paginated Results:

SELECT name, grade FROM students LIMIT 10 OFFSET 20;  

• Sampling Data:

SELECT * FROM students FETCH FIRST 5 ROWS ONLY;  

• Reporting:

SELECT TOP 5 department, SUM(salary) FROM employees GROUP BY department;  

Conclusion

Using SQL clauses like TOP, LIMIT, FETCH FIRST, or ROWNUM, you can efficiently limit the number of rows returned by a query, enhancing performance and usability. Each clause is suited to specific database systems, so understanding their nuances is essential.

For more SQL insights, tutorials, and tips, visit The Coding College and take your SQL expertise to the next level!