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.

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:

The following CREATE statement creates the Fresher_Marks table:

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

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


Fresher_IDFirst_NameMiddle_NameLast_NameCityAptitude_MarksReasoning_MarksTechnical_MarksPercentage
501VinayRoyGuptaLucknow85927885
502MonuRoySinghaniaChandigarh54689888
504RaviRoyKumarLucknow71826971
507ShyamRoySharmaDelhi85906878
510AbhayKumarGuptaChandigarh45688278
509RiyaRoySharmaDelhi68906991
505VishalKumarSharmaMumbai75658875

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

Output:

Fresher_IDFirst_NameMiddle_NameLast_NameCityAptitude_MarksReasoning_MarksTechnical_MarksPercentage
501VinayRoyGuptaLucknow85927885
502MonuRoySinghaniaChandigarh54689888
504RaviRoyKumarLucknow71826971
507ShyamRoySharmaDelhi85906878
510AbhayKumarGuptaChandigarh45688278

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_IDFirst_NameMiddle_NameLast_NameCityAptitude_MarksReasoning_MarksTechnical_MarksPercentage
509RiyaRoySharmaDelhi68906991
502MonuRoySinghaniaChandigarh54689888
501VinayRoyGuptaLucknow85927885
507ShyamRoySharmaDelhi85906878
510AbhayKumarGuptaChandigarh45688278

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:

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

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


Product_IDProduct_NameProduct_QuantityPurchasing_PriceSelling_PriceRelease_DateProduct_Rating
0.1P10.2500.5NULL2022-04-30NULL
0.2P40.5000.45-0.82022-01-280.25
0.3P20.3500.850.2502022-02-180.15
0.4P70.8500.355NULL2021-12-250.45
0.5P60.9000.5-0.5002021-10-15NULL
0.6P80.7500.110-0.952022-01-28-0.9
0.7P100.2500.5500.2582022-04-11NULL

Query 1: The following SELECT query uses the LIMIT function with the OFFSET keyword with the above table:


Product_IDProduct_NameProduct_QuantityPurchasing_PriceSelling_PriceRelease_DateProduct_Rating
0.4P70.8500.355NULL2021-12-250.45
0.3P20.3500.850.2502022-02-180.15
0.2P40.5000.45-0.82022-01-280.25