A2-02-06.DML-Using MySQL LIMIT to Constrain The Number of Rows Returned By SELECT Statement
转载自：http://www.mysqltutorial.org/mysql-limit.aspx
Using MySQL LIMIT to Constrain The Number of Rows Returned By SELECT Statement
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:
|
1
2
3
4
5
|
SELECT
column1,column2,...
FROM
table
LIMIT offset , count;
|
Let’s examine the LIMIT clause parameters:
- The
offsetspecifies the offset of the first row to return. Theoffsetof the first row is 0, not 1. - The
countspecifies the maximum number of rows to return.
When you use the LIMIT clause with one argument, this argument will be used to determine the maximum number of rows to return from the beginning of the result set.
|
1
2
3
4
5
|
SELECT
column1,column2,...
FROM
table
LIMIT count;
|
The query above is equivalent to the following query with the LIMIT clause that accepts two arguments:
|
1
2
3
4
5
|
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:
|
1
2
3
4
5
|
SELECT
column1,column2,...
FROM
table
LIMIT N;
|
For example, to select the first 10 customers, you use the following query:
|
1
2
3
4
5
6
7
|
SELECT
customernumber,
customername,
creditlimit
FROM
customers
LIMIT 10;
|
Using MySQL LIMIT to get the highest and lowest values
The LIMIT clause often used with the 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 top five customers who have the highest credit limit, you use the following query:
|
1
2
3
4
5
6
7
8
9
|
SELECT
customernumber,
customername,
creditlimit
FROM
customers
ORDER BY
creditlimit DESC
LIMIT 5;
|
And the following query returns five customers who have the lowest credit limit:
|
1
2
3
4
5
6
7
8
9
|
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.
- First, you sort the result set in descending order.
- Second, you use the
LIMITclause to get the nth most expensive product.
The generic query is as follows:
|
1
2
3
4
5
6
|
SELECT
column1, column2,...
FROM
table
ORDER BY column1 DESC
LIMIT nth-1, count;
|
Let’s take a look at an example. We will use the products table in the sample database for the demonstration.
See the following products result set:
|
1
2
3
4
5
6
7
|
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)
|
1
2
3
4
5
6
7
8
|
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.
