LIMIT Function in SQL

The LIMIT function in Structured Query Language returns the records from the table according to the specified limit value.

All the SQL version does not support the LIMIT function. It is important to note that the value of LIMIT must be a non-negative integer.

Syntax of LIMIT Function

In SQL, we can use the LIMIT function with the columns of the string as well as integers.

SELECT Column_Name1, Column_Name2, Column_Name3, ……., Column_NameN FROM Table_Name LIMIT Value;

In this syntax, we have to the specify the LIMIT keyword with its value after the name of the table.

Examples of LIMIT function

Example 1: This example uses the LIMIT function with the table in Structured Query Language.

The following block shows how to create the new table in Structured Query Language:

CREATE TABLE Name_of_New_Table
(
First_Column_of_table Data Type (character_size of First Column),  
Second_Column_of_table Data Type (character_size of the Second column ),  
Third_Column_of_table Data Type (character_size of the Third column),  
??.,  
Last_Column_of_table Data Type (character_size of the Last column)
);  

The following CREATE statement creates the Fresher_Marks table:

CREATE TABLE Fresher_Marks
(
Fresher_ID INT PRIMARY KEY,  
First_Name VARCHAR (100),  
Middle_Name Varchar(120),
Last_Name VARCHAR (200), 
City Varchar(120),
Aptitude_Marks INT, 
Reasoning_Marks INT, 
Technical_Marks INT,
Percentage INT
);

The below INSERT queries insert the records of freshers with marks and details in the Fresher_Marks table:

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (501, Vinay, Roy, Gupta, Lucknow, 85, 92, 78, 85 );

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (502, Monu, Roy, Singhania, Chandigarh, 54, 68, 98, 88 );

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (504, Ravi, Roy, Kumar, Lucknow, 71, 82, 69, 71 );

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (507, Shyam, Roy, Sharma, Delhi, 85, 90, 68, 78 );

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (510, Abhay, Kumar, Gupta, Chandigarh, 45, 68, 82, 78);

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (509, Riya, Roy, Sharma, Delhi, 68, 90, 69, 91 );

INSERT INTO Fresher_Marks (Fresher_ID, First_Name, Middle_Name, Last_Name, City, Aptitude_Marks, Reasoning_Marks, Technical_Marks, Percentage) VALUES (505, Vishal, Kumar, Sharma, Mumbai, 75, 65, 88, 75 );

The following SELECT statement displays the inserted records of the above Fresher_Marks table:

Fresher_ID	First_Name	Middle_Name	Last_Name	City	Aptitude_Marks	Reasoning_Marks	Technical_Marks	Percentage
501	Vinay	Roy	Gupta	Lucknow	85	92	78	85
502	Monu	Roy	Singhania	Chandigarh	54	68	98	88
504	Ravi	Roy	Kumar	Lucknow	71	82	69	71
507	Shyam	Roy	Sharma	Delhi	85	90	68	78
510	Abhay	Kumar	Gupta	Chandigarh	45	68	82	78
509	Riya	Roy	Sharma	Delhi	68	90	69	91
505	Vishal	Kumar	Sharma	Mumbai	75	65	88	75

Query 1: The following SELECT query uses the LIMIT keyword to show the first five rows of the above table:

Output:

Fresher_ID	First_Name	Middle_Name	Last_Name	City	Aptitude_Marks	Reasoning_Marks	Technical_Marks	Percentage
501	Vinay	Roy	Gupta	Lucknow	85	92	78	85
502	Monu	Roy	Singhania	Chandigarh	54	68	98	88
504	Ravi	Roy	Kumar	Lucknow	71	82	69	71
507	Shyam	Roy	Sharma	Delhi	85	90	68	78
510	Abhay	Kumar	Gupta	Chandigarh	45	68	82	78

Query 2: The following SELECT query uses the LIMIT keyword in the ORDER BY clause with the DESC query to show the last five percentage:

Output:

Fresher_ID	First_Name	Middle_Name	Last_Name	City	Aptitude_Marks	Reasoning_Marks	Technical_Marks	Percentage
509	Riya	Roy	Sharma	Delhi	68	90	69	91
502	Monu	Roy	Singhania	Chandigarh	54	68	98	88
501	Vinay	Roy	Gupta	Lucknow	85	92	78	85
507	Shyam	Roy	Sharma	Delhi	85	90	68	78
510	Abhay	Kumar	Gupta	Chandigarh	45	68	82	78

LIMIT Function with OFFSET

The OFFSET keyword with LIMIT function specify that row from where data is to be shown. The value of OFFSET can never be negative, otherwise it returns error.

We can specify the value of OFFSET as zero or greater than zero.

Example of LIMIT Function with OFFSET

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

CREATE TABLE Product_Details
(
Product_ID INT NOT NULL,
Product_Name Varchar(50),
Product_Quantity INT,
Purchasing_Price INT,
Selling_Price INT,
Release_Date Date, 
Product_Rating INT
);

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the Product_Details table:

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (0.1, P1, 0.250, 0.5, NULL, 2022-04-30, NULL);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (0.2, P4, 5.500, 0.45, -0.8, 2022-01-28, 0.25);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (0.3, P2, 0.350, 0.85, 0.250, 2022-02-18, 0.15);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (0.4, P7, 0.850, 0.355, NULL, 2021-12-25, 0.45);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (0.5, P6, 0.900, 0.5, -0.500, 2021-10-15, NULL);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (0.6, P8, 0.750, 0.110, -0.95, 2022-01-28, -0.9);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (0.7, P10, 0.250, 0.550, 0.258, 2022-04-11, NULL);

The following SELECT statement displays the inserted records of the above Product_Details table:

Product_ID	Product_Name	Product_Quantity	Purchasing_Price	Selling_Price	Release_Date	Product_Rating
0.1	P1	0.250	0.5	NULL	2022-04-30	NULL
0.2	P4	0.500	0.45	-0.8	2022-01-28	0.25
0.3	P2	0.350	0.85	0.250	2022-02-18	0.15
0.4	P7	0.850	0.355	NULL	2021-12-25	0.45
0.5	P6	0.900	0.5	-0.500	2021-10-15	NULL
0.6	P8	0.750	0.110	-0.95	2022-01-28	-0.9
0.7	P10	0.250	0.550	0.258	2022-04-11	NULL