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

SELECT TOP number | percent column_Name1, column_Name2, ....., column_NameN  FROM table_name WHERE [Condition] ;  

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

SELECT column_Name1,column_Name2, ....., column_NameN FROM table_name LIMIT value;  

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

SELECT column_Name1,column_Name2, ....., column_NameN FROM table_name WHERE ROWNUM <= value;  

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