TOP in SQL

The SQL SELECT TOP command retrieves records from one or more tables in a database and allows us to limit the query result set based on the number of rows or the percentage of rows. This clause is used when the database tables contain thousands of records. In most cases, the TOP and ORDER BY constructions are used in conjunction. Otherwise, the TOP clause will return the first N rows in an arbitrary order. As a result, it is best practice to utilize the TOP clause in conjunction with an ORDER BY to produce a specific sorted result.

Consider the following example for a better understanding of TOP in SQL:

If a Student table contains a large quantity of data about students, the select TOP statement decides how much students' data will be fetched from the provided database. In this tutorial, we will learn how to use SQL SELECT TOP queries and strengthen our understanding with examples.

How Does TOP Work In SQL?

TOP in SQL (MySql LIMIT, Oracle ROWNUM) is a very useful command for selecting and filtering rows in large tables with thousands of records. This TOP keyword comes in use when we do not want to retrieve all of the rows that meet the requirement, as if there are millions of entries and you execute only the SELECT command, the query will take a long time and close your Query window. This keyword is also used to retrieve the top (n-highest) and lowest (n-lowest) data from a table.

When you use TOP in conjunction with INSERT, UPDATE, MERGE, or DELETE, the referenced rows are not sorted in any particular order. Also, you cannot explicitly define the ORDER BY clause in these statements. If you need to use TOP to insert, delete, or alter rows in relevant chronological order. In that case, it is recommended to use TOP with an ORDER BY clause specified in a subselect statement.

Syntax

Examples For TOP In SQL

The five SQL examples below will show you how to use the TOP in SQL command with PERCENT and WITH TIES keyword in the SQL :

Using the TOP keyword

Example - 1 : In this example, we have three columns in a Cars table, namely Car_Name, Car_Color, and Car_Cost :

Assume you wish to display the first three-car names and colours from the table above. To accomplish this, enter the following TOP in the SQL query:

In the SQL output, this query displays the following table on the screen :

Example - 2 : In this example, we have three columns in a Student table, namely Student_ID, Student_Name, and Student_Marks :

Assume you want to display the details of the first four students from the above table. To accomplish this, enter the following TOP in the SQL query :

In the SQL output, this query displays the following table on the screen :

Example - 3: In this example, we have five columns in a Customer table, namely ID, NAME, AGE, ADDRESS, and SALARY :

Assume you want to display the details of the first three customers from the above table. To accomplish this, enter the following TOP in the SQL query :

In the SQL output, this query displays the following table on the screen :

Using the TOP PERCENT keyword

Example - 4: In this example, we have three columns in a Bikes table, namely Bike_Name, Bike_Color, and Bike_Cost :

Assume you want to display 50 percent data from the above table. To accomplish this, enter the following TOP in the SQL query :

In the SQL output, this query displays the following table on the screen :

Using the TOP WITH TIES keyword

Example - 5 : In this example, we have four columns in a Beverages table, namely Product_Name, Product_Volume, Product_Price, and Product_Quantity :

In the SQL output, this query returns the top three most expensive products :

The third most costly product has a list price of $29.00$. Since the TOP in SQL command is used WITH TIES, it returned three more products with the same list price as the third.

Learn more

• SQL Tutorial