MySQL LIMIT

Summary: in this tutorial, you will learn how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.

Introduction to 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.

The following illustrates the LIMIT clause syntax with two arguments:

SELECT 
    column1,column2,...
FROM
    table
LIMIT offset , count;

Let’s examine the LIMIT clause parameters:

• The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
• The count specifies the maximum number of rows to return.

When you use LIMIT with one argument, this argument will be used to specifies the maximum number of rows to return from the beginning of the result set.

SELECT 
    column1,column2,...
FROM
    table
LIMIT count;

The query above is equivalent to the following query with the LIMIT clause that accepts two arguments:

SELECT 
    column1,column2,...
FROM
    table
LIMIT 0 , count;

Using MySQL LIMIT to get the first N rows

You can use the LIMIT clause to select the first N  rows in a table as follows:

SELECT 
    column1,column2,...
FROM
    table
LIMIT N;

For example, to select the first 10 customers, you use the following query:

SELECT
 customernumber,
 customername,
 creditlimit
FROM
 customers
LIMIT 10;

Using MySQL LIMIT to get the highest and lowest values

The LIMIT clause often used with ORDER BY clause. First, you use the ORDER BY  clause to sort the result set based on certain criteria, and then you use the LIMIT clause to find lowest or highest values.

See the following customers table in the sample database.

For example, to select 5 customers who have the highest credit limit, you use the following query:

SELECT
 customernumber,
 customername,
 creditlimit
FROM
 customers
ORDER BY
 creditlimit DESC
LIMIT 5;

And the following query returns 5 customers who have the lowest credit limit:

SELECT
 customernumber,
 customername,
 creditlimit
FROM
 customers
ORDER BY
 creditlimit ASC
LIMIT 5;

Using 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.

1. First, you sort the result set in descending order.
2. Second, you use the LIMIT clause to get the nth most expensive product.

The generic query is as follows:

SELECT 
    column1, column2,...
FROM
    table
ORDER BY column1 DESC
LIMIT nth-1, count;

Let’s take a look at an example.

See the following products result set:

SELECT
 productName,
 buyprice
FROM
 products
ORDER BY
 buyprice DESC;

Our task is to get the highlight product, which is the second most expensive product in the result set. In order to do so, you use LIMIT clause to select the second row as the following query: (notice that the offset starts from zero)

SELECT
 productName,
 buyprice
FROM
 products
ORDER BY
 buyprice DESC
LIMIT 1, 1;

In this tutorial, we have shown you how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement. We also introduced you to a technique to get the nth highest value.

原文：http://www.mysqltutorial.org/mysql-limit.aspx

标签: MySQL

«上一篇： CSS2中的伪类与伪元素
»下一篇： Async