mysql limit clause

Rate this post

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.


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.
    productName, buyprice
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.
productName, buyprice
ORDER BY buyprice DESC
LIMIT 1 , 1;
Output :



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:


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 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
  • 37
    Birth Of MySQL MySQL started out with the intention of using the mSQL database system to connect to tables using fast low-level (ISAM) routines. However, after some testing, They conclude that mSQL was not fast enough or flexible enough. This resulted in a new SQL interface to our database but with…
    Tags: mysql, sql
  • 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: Master-2: 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: