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:
MySQL-LIMITexample on table
MySQL-LIMITexample on table
  • 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;
Output :
mysql-limit-most-second-expensive-product

mysql-limit-most-second-expensive-product

 

Understanding the MySQL Limit:

mysql-limit-offset understanding
mysql-limit-offset understanding

 

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;

Satya Prakash

VOIP Expert: More than 8 years of experience in Asterisk Development and Call Center operation Management. Unique Combination of Skill Set as IT, Analytics and operation management.

Leave a Reply