DbSchema Tutorial | SQL TOP, LIMIT, FETCH FIRST, and ROWNUM Clauses
In SQL, sometimes we don’t want to retrieve all the rows from a table. Instead, we want to limit the result set to a specific number of rows. This is where the SQL TOP, LIMIT, FETCH FIRST, and ROWNUM clauses come into play. These clauses help us to retrieve a fixed number of rows from a table.
Sample Database Table
Before we begin, let’s create a sample database table. Below is a Students table which we will use throughout this tutorial.
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 20 |
| 2 | Jane | Doe | 22 |
| 3 | Sam | Smith | 19 |
| 4 | Mike | Johnson | 21 |
| 5 | Emily | Davis | 23 |
SQL SELECT TOP Clause
The SELECT TOP clause is used to specify the number of records to return from a table. This clause is mainly used in MS SQL Server and MS Access.
Syntax
SELECT TOP number|percent columns
FROM table
WHERE condition;Example
Let’s retrieve the top 3 students from the ‘Students’ table.
SELECT TOP 3 * FROM Students;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 20 |
| 2 | Jane | Doe | 22 |
| 3 | Sam | Smith | 19 |
Explanation
In the above query, the TOP clause fetches the first three records from the Students table.
SQL TOP PERCENT
The TOP PERCENT clause is used to specify the percentage of records to return from a table.
Syntax
SELECT TOP percent PERCENT columns
FROM table
WHERE condition;Example
Let’s retrieve the top 50% of students from the Students table.
SELECT TOP 50 PERCENT * FROM Students;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 20 |
| 2 | Jane | Doe | 22 |
| 3 | Sam | Smith | 19 |
Explanation
Since there are 5 rows in the Students table, 50% of 5 is 2.5, so the query retrieves the first 3 rows.
SQL TOP PERCENT with WHERE Clause
You can combine the TOP PERCENT clause with the WHERE clause to filter the results.
Syntax
SELECT TOP percent PERCENT columns
FROM table
WHERE condition;Example
Let’s retrieve the top 50% of students from the Students table whose age is greater than or equal to 21.
SELECT TOP 50 PERCENT * FROM Students
WHERE Age >= 21;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 2 | Jane | Doe | 22 |
| 5 | Emily | Davis | 23 |
Explanation
In the Students table, there are three students whose age is greater than or equal to 21. Therefore, 50% of 3 is 1.5, so the query retrieves the first 2 rows.
Using SQL UPDATE TOP statement
The UPDATE TOP statement is used to update a specified number of records in a table.
Syntax
UPDATE TOP (number) table
SET column1 = value1, column2 = value2, ...
WHERE condition;Example
Let’s update the age of the top 2 students in the Students table to 25.
UPDATE TOP (2) Students
SET Age = 25;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 25 |
| 2 | Jane | Doe | 25 |
| 3 | Sam | Smith | 19 |
| 4 | Mike | Johnson | 21 |
| 5 | Emily | Davis | 23 |
Explanation
In the above query, the UPDATE TOP statement updates the age of the first two students to 25.
Using SQL DELETE TOP statements
The DELETE TOP statement is used to delete a specified number of records from a table.
Syntax
DELETE TOP (number)
FROM table
WHERE condition;Example
Let’s delete the top 2 students from the Students table.
DELETE TOP (2)
FROM Students;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 3 | Sam | Smith | 19 |
| 4 | Mike | Johnson | 21 |
| 5 | Emily | Davis | 23 |
Explanation
In the above query, the DELETE TOP statement deletes the first two students from the Students table.
SQL LIMIT Clause
The LIMIT clause is used to limit the number of rows returned by a query. It is widely used in databases like MySQL, PostgreSQL, and SQLite.
Syntax
SELECT columns
FROM table
WHERE condition
LIMIT number;Example
Let’s retrieve the first 3 students from the Students table.
SELECT * FROM Students
LIMIT 3;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 25 |
| 2 | Jane | Doe | 25 |
| 3 | Sam | Smith | 19 |
Explanation
In the above query, the LIMIT clause fetches the first three records from the Students table.
SQL LIMIT with WHERE Clause
You can combine the LIMIT clause with the WHERE clause to filter the results.
Syntax
SELECT columns
FROM table
WHERE condition
LIMIT number;Example
Let’s retrieve the first 2 students from the Students table whose age is less than 25.
SELECT * FROM Students
WHERE Age < 25
LIMIT 2;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 3 | Sam | Smith | 19 |
| 4 | Mike | Johnson | 21 |
Explanation
In the above query, the WHERE clause filters the students whose age is less than 25, and then the LIMIT clause fetches the first two records from the filtered results.
SQL LIMIT With OFFSET Clause
The OFFSET clause is used to skip a specific number of rows before starting to return rows from the query. The LIMIT and OFFSET clauses are often used together to paginate the results.
Syntax
SELECT columns
FROM table
WHERE condition
LIMIT number OFFSET number;Example
Let’s retrieve 2 students from the Students table after skipping the first 2 students.
SELECT * FROM Students
LIMIT 2 OFFSET 2;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 3 | Sam | Smith | 19 |
| 4 | Mike | Johnson | 21 |
Explanation
In the above query, the OFFSET clause skips the first two students, and then the LIMIT clause fetches the next two students.
SQL FETCH FIRST n ROWS ONLY Clause
The FETCH FIRST n ROWS ONLY clause is used to fetch the first n rows from a table. It is supported by databases like IBM DB2, Oracle, and PostgreSQL.
Syntax
SELECT columns
FROM table
WHERE condition
FETCH FIRST n ROWS ONLY;Example
Let’s retrieve the first 3 students from the Students table.
SELECT * FROM Students
FETCH FIRST 3 ROWS ONLY;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 25 |
| 2 | Jane | Doe | 25 |
| 3 | Sam | Smith | 19 |
Explanation
In the above query, the FETCH FIRST n ROWS ONLY clause fetches the first three students from the Students table.
SQL FETCH FIRST PERCENT
The FETCH FIRST PERCENT clause is used to fetch a percentage of rows from a table.
Syntax
SELECT columns
FROM table
WHERE condition
FETCH FIRST percent PERCENT ROWS ONLY;Example
Let’s retrieve the first 50% of students from the Students table.
SELECT * FROM Students
FETCH FIRST 50 PERCENT ROWS ONLY;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 25 |
| 2 | Jane | Doe | 25 |
| 3 | Sam | Smith | 19 |
Explanation
Since there are 5 rows in the Students table, 50% of 5 is 2.5, so the query retrieves the first 3 rows.
SQL FETCH FIRST with WHERE Clause
You can combine the FETCH FIRST clause with the WHERE clause to filter the results.
Syntax
SELECT columns
FROM table
WHERE condition
FETCH FIRST n ROWS ONLY;Example
Let’s retrieve the first 2 students from the Students table whose age is greater than or equal to 21.
SELECT * FROM Students
WHERE Age >= 21
FETCH FIRST 2 ROWS ONLY;Results
| StudentID | FirstName | LastName | Age |
|---|---|---|---|
| 1 | John | Doe | 25 |
| 2 | Jane | Doe | 25 |
Explanation
In the above query, the WHERE clause filters the students whose age is greater than or equal to 21, and then the FETCH FIRST clause fetches the first two students from the filtered results.
Common Mistakes
Using the wrong clause for your database: The TOP clause is specific to MS SQL Server and MS Access, the LIMIT clause is used in MySQL, PostgreSQL, and SQLite, and the FETCH FIRST clause is used in IBM DB2, Oracle, and PostgreSQL. Make sure to use the correct clause for your database.
Using OFFSET without LIMIT: The OFFSET clause should always be used with the LIMIT clause. If you use OFFSET without LIMIT, you will get an error.
Using a negative number with LIMIT, OFFSET, or FETCH FIRST: The LIMIT, OFFSET, and FETCH FIRST clauses do not accept negative numbers. If you use a negative number, you will get an error.
FAQs
- Can I use the TOP, LIMIT, and FETCH FIRST clauses with the UPDATE and DELETE statements?
- The TOP clause can be used with the UPDATE and DELETE statements in MS SQL Server. However, the LIMIT and FETCH FIRST clauses cannot be used with the UPDATE and DELETE statements.
- What is the difference between the TOP and FETCH FIRST clauses?
- The TOP clause is specific to MS SQL Server and MS Access, whereas the FETCH FIRST clause is used in IBM DB2, Oracle, and PostgreSQL. Both clauses serve the same purpose, which is to limit the number of rows returned by a query.
- Can I use the OFFSET clause with the TOP and FETCH FIRST clauses?
- The OFFSET clause can only be used with the LIMIT clause. It cannot be used with the TOP and FETCH FIRST clauses.
Practice Questions
Write a SQL query to retrieve the first 2 students from the ‘Students’ table whose age is less than 21.
Write a SQL query to update the age of the first 3 students in the ‘Students’ table to 30.
Write a SQL query to delete the first student from the ‘Students’ table.
Write a SQL query to retrieve the top 50% of students from the ‘Students’ table whose age is greater than or equal to 20.
Conclusion
In this tutorial, we have learned about the SQL TOP, LIMIT, FETCH FIRST, and ROWNUM clauses, which are used to limit the number of rows returned by a query. We have also looked at examples of how to use these clauses with the SELECT, UPDATE, and DELETE statements.
Make sure to practice the queries in your own database to get a better understanding of these clauses.
Happy querying !!
