SQL – TOP, LIMIT, FETCH FIRST Clause

The 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. 

SQL-SELECT TOP Clause

The SELECT TOP clause returns only the selected number of 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,
   Age INT NOT NULL,
   Salary MONEY NOT NULL
);

Step 4: Adding data to the table

Query:

INSERT INTO Employee (EmpId, EmpName, Email, Address, Age, Salary)
VALUES (1, 'Shubham', 'shubham@example.com', 'India', 23, 50000.00),
       (2, 'Aman', 'aman@example.com', 'Australia', 21, 45000.00),
       (3, 'Naveen', 'naveen@example.com', 'Sri Lanka', 24, 55000.00),
       (4, 'Aditya', 'aditya@example.com', 'Austria', 21, 42000.00),
       (5, 'Nishant Saluja', 'nishant@example.com', 'Spain', 22, 48000.00);

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:

MYSQL:

SELECT CustomerName, Country
FROM Customer
ORDER BY Age DESC
LIMIT 4;

Microsoft SQL Server

SELECT TOP 4*
FROM Customer;

Output:

MySQL

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

Query:

MySQL

SELECT CustomerName, Age
FROM Customer
ORDER BY Age DESC
LIMIT 4;

Microsoft SQL Server

SELECT TOP 4*
FROM Customer
ORDER BY Salary DESC;

Output:

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

Query:

MySQL

SELECT * FROM Employee 
WHERE Age >= 22 
ORDER BY Salary ASC 
LIMIT 2;

Microsoft SQL Server 

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 and percent of all-out rows. For instance,

Query:

Microsoft SQL Server

SELECT TOP 50 PERCENT*
FROM Employee;

MySQL

SELECT *
FROM Employee
LIMIT (SELECT CEIL(COUNT(*) / 2) FROM Employee);

Output:

MySQL

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:

Microsoft SQL Server

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

MySQL

SELECT *
FROM Employee
WHERE Salary < 50000
ORDER BY Salary
LIMIT (SELECT CEIL(COUNT(*) / 2) FROM Employee);

Output:

MySQL

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,
   Age INT NOT NULL,
   Salary MONEY NOT NULL
);

INSERT INTO Employee (EmpId, EmpName, Email, Address, Age, Salary)
VALUES (1, 'Shubham', 'shubham@example.com', 'India', 23, 50000.00),
       (2, 'Aman', 'aman@example.com', 'Australia', 21, 45000.00),
       (3, 'Naveen', 'naveen@example.com', 'Sri Lanka', 24, 55000.00),
       (4, 'Aditya', 'aditya@example.com', 'Austria', 21, 42000.00),
       (5, 'Nishant Saluja', 'nishant@example.com', 'Spain', 22, 48000.00);
    
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
WHERE Salary = 45000
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 = 45000
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 of the third row (i.e., OFFSET 2 means, the initial 2 rows are excluded or avoided).

SQL FETCH FIRST n ROWS ONLY Clause

The “FETCH FIRST” syntax is not supported in MySQL. The correct syntax for limiting the number of rows in MySQL is by using the LIMIT clause.

Query:

SELECT *
FROM Employee
LIMIT 3;

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
LIMIT (SELECT CEIL(COUNT(*) / 2) FROM Employee);

Output:

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

SQL FETCH FIRST with WHERE CLAUSE

The “FETCH FIRST” syntax is not supported in MySQL. The correct syntax for limiting the number of rows in MySQL is by using the LIMIT clause.

Query:

SELECT *
FROM Employee
WHERE Salary = 45000
LIMIT 1;

Output:

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