SQL LIMIT Usage Explained [Practical Examples]


Written by - Falguni Thakker
Reviewed by - Deepak Prasad

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 LIMIT clause , If you use SQL Server, you need to use the SELECT TOP instead 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 with ORDER BY to ensure that the results are deterministic

 

SQL LIMIT Syntax

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 ALL is the same as omitting the LIMIT clause
  • LIMIT number_rows: It specifies a limited number of rows in the result set to be returned based on number_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

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:

SQL LIMIT Usage Explained [Practical Examples]

 

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:

SQL LIMIT Usage Explained [Practical Examples]

 

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:

SQL LIMIT Usage Explained [Practical Examples]

 

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 Usage Explained [Practical Examples]

 

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:

SQL LIMIT Usage Explained [Practical Examples]

 

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:

SQL LIMIT Usage Explained [Practical Examples]

 

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

 

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on LinkedIn .

Categories SQL

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment

X