mysql limit clause
What is MySQL limit clause?
- The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set.
- The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.
- It limits the Data Selections from a MySQL Database.
- The LIMIT clause makes it easy to code multi page results or pagination with SQL, and is very useful on large tables.
- Returning a large number of records can impact on performance.
Example:
MySQL LIMIT to get the first N rows
select * from tablename limit 10;
MySQL LIMIT to get the nth highest value
One of the toughest questions in MySQL is how to select the nth highest values in a result set e.g., select the second (or nth) most expensive product, which you cannot use MAX or MIN functions to answer. However, you can use MySQL LIMIT to answer those kinds of questions.
- First, you sort the result set in descending order.
- Second, you use the LIMIT clause to get the nth most expensive product.
Let’s have an example:
- In first step, we will sort the result set in descending order.
SELECT
productName, buyprice
FROM
products
ORDER BY buyprice DESC;
Now output would be as below:
- As we have to get the output of second highest price of the product. For this we would use limit to get the exact output as below.
SELECT
productName, buyprice
FROM
products
ORDER BY buyprice DESC
LIMIT 1 , 1;
productName, buyprice
FROM
products
ORDER BY buyprice DESC
LIMIT 1 , 1;
Output :
mysql-limit-most-second-expensive-product
Understanding the MySQL Limit:
Point to be Noted:
- Assume we wish to select all records from 1 – 30 (inclusive) from a table called “Orders”. The SQL query would then look like this:
SELECT * FROM Orders LIMIT 30;
When the SQL query above is run, it will return the first 30 records.
- if we want to select records 16 – 25, the SQL query would be as below:
SELECT * FROM Orders LIMIT 10 OFFSET 15;
or
SELECT * FROM Orders LIMIT 15, 10;
