SQL SELECT TOP
The SELECT TOP statement in SQL shows the limited number of records or rows from the database table. The TOP clause in the statement specifies how many rows are returned.
It shows the top N number of rows from the tables in the output. This clause is used when there are thousands of records stored in the database tables.
Let's take a simple example: If a Student table has a large amount of data about students, the select TOP statement determines how much student data will be retrieved from the given table.
Note: All the database systems do not support the TOP keyword for selecting the limited number of records. Oracle supports the ROWNUM keyword, and MySQL supports the LIMIT keyword.
Syntax of TOP Clause in SQL
In the syntax, the number denotes the number of rows shown from the top in the output. column_Name denotes the column whose record we want to show in the output. We can also specify the condition using the WHERE clause.
Examples of TOP Clause in SQL
The following four SQL examples will help you how to use the Number and Percent in SQL TOP clause in the query:
Example 1: In this example, we have a table called Cars with three columns:
| Car Name | Car Color | Car Cost |
|---|---|---|
| Hyundai Creta | White | 10,85,000 |
| Hyundai Venue | White | 9,50,000 |
| Hyundai i20 | Red | 9,00,000 |
| Kia Sonet | White | 10,00,000 |
| Kia Seltos | Black | 8,00,000 |
| Swift Dezire | Red | 7,95,000 |
- Suppose, you want to show the first three Names and Color of Car from the above table. To do this, you have to type the following query in SQL:
-
SELECT TOP 3 Car_Name, Car_Color FROM Cars;
This query shows the following table on the screen:
| Car_Name | Car_Color |
|---|---|
| Hyundai Creta | White |
| Hyundai Venue | White |
| Hyundai i20 | Red |
Example 2: In this example, we have a table called Student with three columns:
| Stu_ID | Stu_Name | Stu_Marks |
|---|---|---|
| 1001 | Abhay | 85 |
| 1002 | Ankit | 75 |
| 1003 | Bheem | 60 |
| 1004 | Ram | 79 |
| 1005 | Sumit | 80 |
- Suppose, you want to show the details of the first four students in the result from the above table. To do this, you have to type the following query in SQL:
-
SELECT TOP 4 * FROM Student;
This query shows the following table on the screen in the SQL output:
| Stu_ID | Stu_Name | Stu_Marks |
|---|---|---|
| 1001 | Abhay | 85 |
| 1002 | Ankit | 75 |
| 1003 | Bheem | 60 |
| 1004 | Ram | 79 |
Example 3: In this example, we have a table called Employee with four columns:
| Emp_Id | Emp_Name | Emp_Salary | Emp_City |
|---|---|---|---|
| 201 | Abhay | 25000 | Goa |
| 202 | Ankit | 45000 | Delhi |
| 203 | Bheem | 30000 | Goa |
| 204 | Ram | 29000 | Goa |
| 205 | Sumit | 40000 | Delhi |
- Suppose, you want to show the details of those first four employees whose city is Goa from the above table. To do this, you have to type the following query in SQL:
-
SELECT TOP 4 * FROM Employee WHERE Emp_City = Goa ;
This query shows the following table on the screen in the SQL output:
| Emp_Id | Emp_Name | Emp_Salary | Emp_City |
|---|---|---|---|
| 201 | Abhay | 25000 | Goa |
| 203 | Bheem | 30000 | Goa |
| 204 | Ram | 29000 | Goa |
Example 4: In this example, we have a table called Bikes with three columns:
| Bike_Name | Bike_Color | Bike_Cost |
|---|---|---|
| KTM DUKE | Black | 185,000 |
| Royal Enfield | Black | NULL |
| Pulsar | Red | 90,0000 |
| Apache | White | NULL |
| Livo | Black | 80,000 |
| KTM RC | Red | 195,000 |
- Suppose, you want to show the 50 percent of data from the above table. To do this, you have to type the following query in SQL:
-
SELECT TOP 50 PERCENT * FROM Bikes;
This query shows the following table on the screen:
| Bike_Name | Bike_Color | Bike_Cost |
|---|---|---|
| KTM DUKE | Black | 185,000 |
| Royal Enfield | Black | NULL |
| Pulsar | Red | 90,0000 |
Syntax of LIMIT Clause in MySQL
In the syntax, we have to specify the value after the LIMIT keyword. The value denotes the number of rows to be shown from the top in the output.
Example of LIMIT Clause in MySQL
The following SQL example will help you how to use the LIMIT clause in the query. In this example, we have a table called Cars with three columns:
| Car Name | Car Color | Car Cost |
|---|---|---|
| Hyundai Creta | White | 10,85,000 |
| Hyundai Venue | White | 9,50,000 |
| Hyundai i20 | Red | 9,00,000 |
| Kia Sonet | White | 10,00,000 |
| Kia Seltos | Black | 8,00,000 |
| Swift Dezire | Red | 7,95,000 |
- Suppose, you want to show the first three records of Car using a LIMIT clause in MySQL. To do this, you have to type the following query in MySQL:
-
SELECT * FROM Cars LIMIT 3;
This query shows the following table on the screen:
| Car Name | Car Color | Car Cost |
|---|---|---|
| Hyundai Creta | White | 10,85,000 |
| Hyundai Venue | White | 9,50,000 |
| Hyundai i20 | Red | 9,00,000 |
Syntax of ROWNUM keyword in WHERE Clause in Oracle database
In the syntax, we have to assign the value to ROWNUM in the WHERE clause. The value denotes the number of rows to be shown from the top in the output.
Example of ROWNUM keyword in WHERE Clause in Oracle
The following SQL example will help you how to use the ROWNUM keyword in the query. In this example, we have a table called Cars with three columns:
| Car Name | Car Color | Car Cost |
|---|---|---|
| Hyundai Creta | White | 10,85,000 |
| Hyundai Venue | White | 9,50,000 |
| Hyundai i20 | Red | 9,00,000 |
| Kia Sonet | White | 10,00,000 |
| Kia Seltos | Black | 8,00,000 |
| Swift Dezire | Red | 7,95,000 |
- Suppose, you want to show the first three records of Car using the ROWNUM keyword in Oracle. To do this, you have to type the following query in the Oracle database:
-
SELECT * FROM Cars WHERE ROWNUM <= 3;
This query shows the following table on the screen:
| Car Name | Car Color | Car Cost |
|---|---|---|
| Hyundai Creta | White | 10,85,000 |
| Hyundai Venue | White | 9,50,000 |
| Hyundai i20 | Red | 9,00,000 |
