SQL - LIMIT (TOP)

---

While we are retrieving data from an SQL table the LIMIT clause is used to restrict the number of rows in the result set. Similarly, if we are performing operations such as update, delete etc. it is used to restrict the target records.

SQL does not support LIMIT clause. Instead, it provides the TOP clause to restrict the number of rows in a table. The TOP clause is similar to the LIMIT clause.

The TOP clause is helpful when we need to work on large tables with thousands of records to get the required values. In this case returning a large number of records might impact the performance.

Not all database systems allow for the TOP clause to be used to select a limited number of records. For example, The MySQL supports the LIMIT clause, whereas Oracle supports the ROWNUM clause.

Syntax

The basic syntax of the SQL TOP clause with a SELECT statement would be as follows −

SELECT TOP value column_name(s)
FROM table_name
WHERE [condition]

Where, value is the number/ percentage of number of rows to return from top.

Using TOP Clause without any condition

We can use the TOP clause without specifying any conditional clauses such as WHERE, ORDER BY, etc.

Example

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
); 

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

The table will be created as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now, we are trying to fetch the top 3 records from the CUSTOMERS table −

SELECT TOP 3 * FROM CUSTOMERS;

Output

This would produce the following result −

+----+----------+-----+----------+----------+
| ID | NAME     | AGE | ADDRESS  | SALARY   |
+----+----------+-----+----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad|  2000.00 |
|  2 | Khilan   |  25 | Delhi    |  1500.00 |
|  3 | Kaushik  |  23 | Kota     |  2000.00 |
+----+----------+-----+----------+----------+

Using TOP Clause with ORDER BY Clause

The ORDER BY clause in SQL is used to sort the result set of a query in ascending or descending order. We can use it along with the TOP clause to retrieve (or, update or, delete etc.) first “n” number of records in sorted order.

Example

Using the following query we are trying to retrieve the top 4 records of the customers table in a sorted order. Here, we are sorting the table in ascending order based on the AGE column −

SELECT TOP 4 * FROM CUSTOMERS ORDER BY AGE ASC;

Output

We obtain the result as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  6 | Komal    |  22 | MP        |  4500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
+----+----------+-----+-----------+----------+

Note − The ORDER BY clause sorts the data in ascending order by default. So, if we need to sort the data in descending order in SQL, we must use DESC keyword.

Using TOP Clause with PERCENT clause

We can also restrict the records by specifying percentage value instead of number,using with the PERCENT clause along with the TOP clause.

Example

The SQL statements given below selects the first 40% of the records from the CUSTOMERS table sorted in the ascending order by their SALARY −

SELECT TOP 40 PERCENT * 
FROM CUSTOMERS
ORDER BY SALARY;

Output

We have the total of 7 records in our table. So 40% of 7 is 2.8. Therefore, SQL server rounds the result to three rows (the next whole number) as shown in the output below −

+----+----------+-----+----------+----------+
| ID | NAME     | AGE | ADDRESS  | SALARY   |
+----+----------+-----+----------+----------+
|  2 | Khilan   |  25 | Delhi    |  1500.00 |
|  3 | Kaushik  |  23 | Kota     |  2000.00 |
|  1 | Ramesh   |  32 | Ahmedabad|  2000.00 |
+----+----------+-----+----------+----------+

Using TOP Clause and WITH TIES Clause

The WITH TIES clause in SQL is used in conjunction with the TOP clause to include tied rows in the result set. The WITH TIES clause allows you to return not just the top N rows in the result set, but all rows that have the same values in the sorted column as the last row in the top N set.

Example

In the following query we are using WITH TIES clause with the TOP clause. Since we have 2 customers with salary equal to 2000, therefore the WITH TIES clause adds all records having the same values and we get 3 records in the result −

SELECT TOP 2 WITH TIES * FROM CUSTOMERS ORDER BY SALARY;

Output

We get 3 records in the output despite adding the TOP 2 clause as shown below −

+----+----------+-----+----------+----------+
| ID | NAME     | AGE | ADDRESS  | SALARY   |
+----+----------+-----+----------+----------+
|  2 | Khilan   |  25 | Delhi    |  1500.00 |
|  3 | Kaushik  |  23 | Kota     |  2000.00 |
|  1 | Ramesh   |  32 | Ahmedabad|  2000.00 |
+----+----------+-----+----------+----------+

Using TOP Clause with WHERE Clause

We can use the TOP clause with the WHERE clause to limit the given number of rows and filter them based on the specified condition.

Example

Suppose, you want to show the details of those first two customers whose name starts with ‘K’ from the CUSTOMERS table. To do this, you have to type the following query in SQL −

SELECT TOP 2 * FROM CUSTOMERS WHERE NAME LIKE 'k%';

Output

Following result is produced −

+----+----------+-----+----------+----------+
| ID | NAME     | AGE | ADDRESS  | SALARY   |
+----+----------+-----+----------+----------+
|  2 | Khilan   |  25 | Delhi    |  1500.00 |
|  3 | Kaushik  |  23 | Kota     |  2000.00 |
+----+----------+-----+----------+----------+

Using DELETE Statement with TOP Clause

In SQL, the TOP clause can be used with the DELETE statement to delete a specific number of rows that meet the given criteria.

Example

In the below query, we are using DELETE statement with TOP clause. Here, we are deleting the top 2 customers whose ADDRESS starts with ‘M’. We specified the TOP 2 clause, so that it should not delete more than 2 records −

DELETE FROM CUSTOMERS
WHERE ADDRESS IN(SELECT TOP 2 ADDRESS FROM CUSTOMERS WHERE ADDRESS LIKE 'M%');

Output

We get the output as shown below −

(2 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the Customers table −

select * from customers;

The table is displayed as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

As we can see in the above table, the customers with the addresses ‘Mumbai’ and ‘MP’ have been deleted from the table.

Uses of TOP Clause

Some common use cases for the LIMIT clause include −

• Pagination − When displaying a large number of records, the TOP clause can be used to return only a certain number of records at a time, allowing for more manageable and efficient pagination of the data.

• Sampling data − The TOP clause can be used to quickly retrieve a sample of data from a table for testing or analysis.

• Improving performance − By limiting the number of rows returned, the TOP clause can help improve the performance of a query, especially when dealing with large tables.

• Debugging − When developing or debugging a query, the TOP clause can be used to quickly return a small number of rows to test the correctness of the query.

• Data visualization − The TOP clause can be used to limit the number of rows returned for visualization purposes, such as creating charts or graphs.