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;

Related Posts

  • 45
    In this post we will see how can we optimize where clause. Where clause restrict the result set to a range. Basically this is used to access the range of a table/view.  The range access method uses a single index to retrieve a subset of table rows that are contained…
    Tags: clause, mysql, select
  • 39
    Here is an example to select multiple column of a table from select statement in asterisk. exten => h,1,MYSQL(Connect conn localhost username password database) exten => h,n,MYSQL(Query res ${conn} 'SELECT call_id,callerid,calltime FROM todaycall WHERE calltime=curdate() order by calltime desc limit 1') exten => h,n,MYSQL(Fetch fid ${r} call_id callerid calltime) exten…
    Tags: select, limit, order, mysql
  • 39
    Syntax: IF(expr,if_true_expr,if_false_expr) MySQL IF function is control flow functions that returns a value based on a condition. The IF function is sometimes referred to as IF ELSE or IF THEN ELSE function. If the expr evaluates to TRUE i.e., expr is not NULL and expr is not 0, the IF…
    Tags: orders, select, mysql
  • 35
    The core logic of a database application is performed through SQL statements, whether issued directly through an interpreter or submitted behind the scenes through an API. The tuning guidelines of this post will help to speed up all kinds of MySQL applications. The guidelines cover SQL operations that read and…
    Tags: select, mysql, sql, set
  • 35
    Here we will help you to set up Master-Master replication between MySQL servers. In this setup if any changes made on either server will update on an other one. Setup Details: Master-1: 10.0.10.12 Master-2: 10.0.10.18 Database: empmaster Step 1. Set Up MySQL Master-1 Server Edit MySQL configuration file and add the following…
    Tags: mysql, set

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

Your email address will not be published. Required fields are marked *

%d bloggers like this: