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:

• 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:

Example 2: In this example, we have a table called Student with three columns:

• 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:

Example 3: In this example, we have a table called Employee with four columns:

• 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:

Example 4: In this example, we have a table called Bikes with three columns:

• 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:

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:

• 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:

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:

• 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: