SQL LIMIT Usage Explained [Practical Examples]

Overview of SQL LIMIT

SQL limit is a clause used with SQL select Statement and is used to set a maximum limit on the number of records returned by SQL select statement, the value specified with SQL LIMIT clause must be a non-negative integer number,

NOTE

SQL LIMIT clause is not supported by all the database systems, MySQL,PostgreSQL,SQLite, Sybase SQL Anywhere, and HSQLDB is supporting SQL LIMITclause , If you use SQL Server, you need to use theSELECT TOPinstead in the place of SQL LIMIT , SQL2008 onwards version is supporting SQL LIMIT clause to restrict number of records in resulting records set

SQL LIMIT clause is used in conjunction withORDER BYto ensure that the results are deterministic

SQL LIMIT Syntax

sql
SELECT column1|expression ,column2|expression...
FROM table_name
[WHERE conditions]
[ ORDER BY sort_expression1 [ASC | DESC] [, sort_expression2 [ASC | DESC] ...] ]
[ LIMIT { number | ALL } ] [ OFFSET number ]

Here,

**Column1|expression:**It is the list of columns or expression to be retrieve
**Table_name:**It is the name of table from which we want to retrieve data , if we want to retrieve data from more than one table we can specified using SQL joins, There must be at least one table listed in the FROM clause
**WHERE conditions:**It is an optional argument with SQL Select, the records which are satisfied this condition will be retrieve
**ORDER BY expression:**It is an optional argument with SQL select clause, it is used in the SELECT LIMIT statement so that you can order the results and target those records that you wish to return. ASC is ascending order and DESC is descending order
**LIMIT ALL:**If specified, the query returns all the rows. In other words, no limit is applied if this option is specified,LIMIT ALLis the same as omitting theLIMITclause
**LIMIT number_rows:**It specifies a limited number of rows in the result set to be returned based onnumber_rows
**OFFSET offset_value:**It is an Optional argument. If specified with LIMIT will be determined where to start resulting data, OFFSET 0 is the same as omitting the OFFSET clause

Examples of SQL LIMIT

Consider online shopping database with three tables to perform practical example on SQL LIMIT

Customer Table

custid	custname	custaddress	custcity	custpincode	custemail	custcontactno
101	reema	gansayamnagar,bhuj	bhuj	395407	reema@gmail.com	45454545
102	kiya	adajan,surat	surat	395007	kiya@gmail.com	63636363
103	priya	pal,adajan	surat	395006	priya@vnsgu.ac.in	63696867
104	leela	rajmarg,delhi	delhi	320001	leela@iitd.ac.in	98989898
105	lissa	bhatar,surat	surat	395017	lissa@vsngu.ac.in	62626262

Product Table

productid	productname	category	tags	price	attribute
10001	bluetooth	electronic	electronic,wired	600	blur color,wired,intel
10002	television	electronic	electronic,colored	30000	LED screen,unbrealable,balck
10003	iphone	electronic	electronic,apple	25000	5.4-inch(13.7 cm diagonal)
10004	smart watch	electronic,watch	electronic, apple	5000	Apple Watch Series 7

Order Table

orderid	orderdate	productid	custid	quntity	shippingaddress	shippingdate	amount
111	02-02-1990	10001	101	2	althan,canal road,surat	12-02-1990	1500
112	03-03-1991	10002	102	1	ambe nagar,delhi	20-03-1991	28000
113	05-05-2000	10001	102	3	bhatar,surat	15-05-2000	2000
114	04-04-2000	10002	103	1	bandra,mumbai	10-04-2000	28000
115	05-04-2000	10003	104	2	bhatar,mumbai	10-04-2000	20000

Use SQL LIMIT to retrieve top 'n' records

Example 1: Write SQL query to display top 3 records of customer

sql
select custid as 'Customer ID' , custname as 'Name',custaddress as 'Address',custcity as 'City',custpincode as 'Pincode',custemail as 'Email',custcontactno as 'Contact No' from tblcust limit 3;

In the above query, SQL LIMIT is used to fetch first three records of customer table

OUTPUT:

Using SQL LIMIT with Order By

Example 2: Write SQL Query to display top two lowest price product information

sql
select productid as 'Product ID' , productname as 'Name',category as 'Product Category' , tags as 'Profuct tags',price as 'Price',attribute as 'Product Attribute' from tblproduct order by price limit 2

In the above query, SQL LIMIT clause is used with order by clause to display records in increment order of price
limit 2 will restrict resulting record set with two records

OUTPUT:

Example 3 : Write a SQL query to retrieve top 3 maximun amount order in descrement order of amount

sql
select orderid as 'Order ID',orderdate as 'Order Date',productid as 'Product ID',custid as 'Customer ID',quntity as 'Quntity' , shippingaddress as 'Shipping Address',shippingdate as 'Shipping Date',amount as 'Amount' from tblorder order by amount desc limit 3;

In the above query, SQL LIMIT clause is used with SQL select to fetch highest three order amount records
ORDER by desc will display resulting record set in decrement order of order amount

OUTPUT:

Using SQL LIMIT with Join

Example 4: Write SQL query to display top three maximum order quantity product details with order date

sql
select tblproduct.productid as 'product ID',productname as 'Product Name',price as 'Product Price',orderdate as 'Order Date',quntity as 'Product Order quntity' from tblproduct left join tblorder on tblproduct.productid=tblorder.productid order by quntity DESC limit 3;

In the above query, SQL LIMIT clause is used with order by clause and left join to retrieve combine record set from two table product and order
To fetch maximum order quantity records order by quantity desc is used and to limit top three order SQL LIMIT clause is used

OUTPUT:

SQL LIMIT with WHERE clause

We can use SQL where clause with SQL LIMIT to conditionally retrieve records set and limit the number of resulting records in the output

Example 5: Write SQL query to display top two maximum order amount customer details whose is living in ‘surat’ city

sql
select custname as 'Customer Name',custaddress as 'Customer Address',custcity as 'Customer city',orderdate as 'Order date' , quntity as 'Order Quntity',amount as 'Total amount' from tblcust left join tblorder on tblcust.custid=tblorder.custid where custcity='surat' order by amount desc limit 2;

In the above query, SQL LIMIT is used with left join, where and order by clause to conditionally retrieve resulting record set in the order of order amount if the customer city is ‘surat’
SQL left join is used to make combine record set of two table customer and order, where cluse is used to check city value as ‘surat’ , order by desc clause is used to

OUTPUT:

How to Use SQL LIMIT Along with Offset?

If bothOFFSETandLIMITappear, thenOFFSETrows are skipped before starting to count theLIMITrows that are returned.

When usingLIMIT, it is important to use anORDER BYclause that constrains the result rows into a unique order. Otherwise, you will get an unpredictable subset of the query's rows. You may be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specifiedORDER BY

Example 6: Write SQL query to display customer order details starts from the second lowest order amount to two records

sql
select custname as 'Customer Name',custaddress as 'Customer Address',custcity as 'Customer city',orderdate as 'Order date' , quntity as 'Order Quntity',amount as 'Total amount' from tblcust left join tblorder on tblcust.custid=tblorder.custid  order by amount  limit 2 offset 2;

In the above query, SQL LIMIT is used to get top two records of customer with their order details starting from second minimum order amount
SQL left join in the previous query is applied to fetch combine records of two tables customer and order, SQL Order by clause is used to fetch records in the order of amount
Offset 2 will set the resulting record starting from the second position and LIMIT 2 will restrict resulting record set to two

OUTPUT:

Summary

In this article, we have explained SQL LIMIT clause starting from overview and it's usage with syntax and discuss used of each argument of syntax. We have covered practical examples of SQL LIMIT starts with simple example LIMIT with number of rows , order by clause, left outer join , where clause and in the ending section we have covered how to use OFFSET clause with SQL LIMIT with example

SQL Limit Clause