SQL LIMIT Usage Explained [Practical Examples]

Use SQL LIMIT to retrieve top 'n' records

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

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

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

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

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

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 both OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows that are returned.

When using LIMIT, it is important to use an ORDER BY clause 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 specified ORDER BY

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

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

Read More

SQL Limit Clause