SQL – TOP, LIMIT, FETCH FIRST Clause

Structured Query Language is a computer language that we use to interact with a relational database.SQL is a tool for organizing, managing, and retrieving archived data from a computer database. T

SQL-SELECT TOP Clause:

The SELECT TOP clause returns only the selected number (or) percentage of selected rows present in the record. It is valuable on enormous tables with a large number of records. Returning countless records can affect execution.

Note: Not all database systems support the SELECT TOP clause.

The TOP keyword is utilized with the accompanying database systems:

• SQL Server
• MS Access 

Let’s understand this using an example.

Step 1: Creating the Database in SQL Server. Use the below SQL statement to create a database called testdb:

Query:

CREATE DATABASE testdb;

Step 2: Using the Database. Use the below SQL statement to switch the database context to testdb:

Query:

USE testdb;

Step3: Table Definition

Query:

CREATE TABLE Employee (
   EmpId INTEGER PRIMARY KEY, 
   EmpName VARCHAR(225) NOT NULL,  
   Email VARCHAR(225) NOT NULL,   
   Address VARCHAR(225) NOT NULL,
   Salary MONEY NOT NULL
);

Step 4: Adding data to the table

Query:

INSERT INTO Employee(EmpId,EmpName,Email,Address,Salary)
VALUES(1,'Rama','rama@gmail.com','Ahmedabad',2000),
     (2,'Kalam','Kalam@gmail.com','Delhi',1500),
     (3,'Komal','komal@gmail.com','Hyderabad',2000),
     (4,'Karan','kar@gmail.com','Kota',6500),
     (5,'Hardik','har@gmail.com','Mumbai',8500),
     (6,'chaitu','chai@gmail.com','Bhopal',5500);

Step 5: We can use the below statement to see the all contents of the created table:

Query:

SELECT * FROM Employee;

Output:

Now we see Top Count with where condition.

Syntax:

SELECT column1, column2, … TOP count

FROM table_name

[WHERE conditions]

[ORDER BY expression [ ASC | DESC ]];

In the above syntax, WHERE conditions are optional conditions that must be true for the records to be selected. ORDER BY expression is an optional statement in the query used to return the result in either ascending or descending order according to the keyword (ASC or DESC). The TOP count is used with the SELECT statement to restrict the number of rows in the result set.  

Let’s understand this using some example queries. The accompanying SQL statement selects the initial 4 records from the table:

Query:

SELECT TOP 4*
FROM Employee;

Output:

The TOP operator can be used in situations such as the above query, where we need to find the Top 4 employees’ records from the table.

Query:

SELECT TOP 4*
FROM Employee
ORDER BY Salary DESC;

Output:

Here, from the above query, the TOP operator selects the top 4 employees with a maximum salary and does not need to use any conditional statements. ORDER BY Salary DESC will sort the records in descending order and using TOP 4 we got the first 4 rows from the sorted result.

We can also include some situations using the WHERE clause in the above query. Suppose we don’t want to get employees whose Salary is less than 2000 in our result set and want the first 2 employees to have low Scores.

Query:

SELECT TOP 2*
FROM Employee
WHERE Salary>2000
ORDER BY Salary; 

Output:

The above query will select all the employees according to the given condition (i.e. all Employees except the employee whose salary is less than 2000 will be selected) then the result would be sorted by Salary in ascending order (The ORDER BY keyword sorts the records in ascending order by default). Finally, the first 2 rows would be returned by the above query.

SQL TOP PERCENT:

The PERCENT keyword is utilized to select the primary n percent of all-out rows. For instance,

Query:

SELECT TOP 50 PERCENT*
FROM Employee;

Output:

Here, the above query will select the first 50% of employee records out of the total number of records(i.e., the first 3 rows will be returned).

SQL TOP PERCENT with WHERE Clause:

We can also include some situations using the TOP PERCENT with the WHERE clause in the above query.

Query:

SELECT TOP 50 PERCENT*
FROM Employee
WHERE Salary<5000;

Output:

The above query will select the Top 50% of the records out of the total number of records from the table according to the given condition such that it returns only the Top 50% of the records with the employee whose salary is less than 5000 (i.e, 2 rows will be returned)

SQL LIMIT Clause:

We simply use the LIMIT command to limit the number of results that will return us a number of rows. The LIMIT Clause is utilized with the accompanying database systems:

• MySQL
• PostgreSQL
• SQLite

Since the LIMIT Clause is not supported in SQL Server we need to create a table in MySQL/PostgreSQL/SQLite. By following the above(SQL Server) steps we can create a table in the MySQL database.

CREATE TABLE Employee (
   EmpId INTEGER PRIMARY KEY, 
   EmpName VARCHAR(225) NOT NULL,  
   Email VARCHAR(225) NOT NULL,   
   Address VARCHAR(225) NOT NULL,
   Salary MONEY NOT NULL
);

INSERT INTO Employee(EmpId,EmpName,Email,Address,Salary)
VALUES(1,'Rama','rama@gmail.com','Ahmedabad',2000),
     (2,'Kalam','Kalam@gmail.com','Delhi',1500),
     (3,'Komal','komal@gmail.com','Hyderabad',2000),
     (4,'Karan','kar@gmail.com','Kota',6500),
     (5,'Hardik','har@gmail.com','Mumbai',8500),
     (6,'chaitu','chai@gmail.com','Bhopal',5500);
    
Select * from employee

Output:

Now let’s see how to use the limit query here. For this, we use SELECT with LIMIT  in MySQL.

Query:

SELECT *
FROM Employee
LIMIT 2;

Output:

From the above query, the LIMIT operator limits the number of records to be returned. Here, it returns the first 2 rows from the table.

SQL LIMIT with WHERE Clause:

The accompanying query selects the initial 4 records from the Employee table with a given condition.

Query:

SELECT *
FROM Employee
WHERE Salary=2000 
LIMIT 2;

Output:

The above query will select all the employees according to the imposed condition (i.e. it selects the limited 2 records from the table where salary is 2000). Finally, the first 2 rows would be returned by the above query.

SQL LIMIT With OFFSET Clause:

The OFFSET keyword is utilized to indicate beginning rows from where to select rows. For instance,

Query:

SELECT *
FROM Employee
LIMIT 2 OFFSET 2;

Output:

Here, the above query selects 2 rows from the beginning from the third row (i.e., OFFSET 2 means, the initial 2 rows are excluded or avoided).

SQL FETCH FIRST n ROWS ONLY Clause:

The Oracle database system utilizes the FETCH FIRST n ROWS ONLY Clause. 

Query:

SELECT * FROM Employee
FETCH FIRST 3 ROWS ONLY;

Output:

Here, the above query will fetch the first 3 rows only from the table. We can also include some situations using the FETCH FIRST PERCENT and WHERE Clause in the above query.

SQL FETCH FIRST PERCENT:   

Query:

SELECT * FROM Employee
FETCH FIRST 50 PERCENT ROWS ONLY;

Output:

Here, the above query fetches the first 50% of a total number of rows (i.e., 3 rows) from the table.

SQL FETCH FIRST with WHERE CLAUSE:

Query:

SELECT * FROM Employee
WHERE Salary=2000
FETCH FIRST 1 ROWS ONLY;

Output:

Here, the above query fetches the first 1 row from the table, with the condition where the salary is 2000 (i.e., it returns 1 row only).