mysql limit使用介绍

Summary: in this tutorial, you will learn how to select the n^th highest record in a database table using various techniques.

It is very easy to select the highest or lowest record in the database table with MAX or MIN function. However it’s a little bit tricky to select the Nth highest record.

In order to select the n^th highest record, you  need to perform the following steps:

• First you get the n highest records and sort them in ascending order. The n^th highest record is the last record in the result set.

• Then you sort the result set in descending order and get the first one.

Here is the query to get the n highest records in the ascending order:

SELECT *  FROM table_name ORDER BY column_name ASC LIMIT n

The query to get the n^th highest record is as follows:

SELECT *  FROM ( SELECT *  FROM table_name ORDER BY column_name ASC LIMIT n ) AS tbl ORDER BY column_name DESC LIMIT 1

Fortunately, MySQL provides us with the LIMIT clause so you just leverage its functionality to rewrite the query as follows:

SELECT * FROM table_name ORDER BY column_name DESC LIMIT n - 1, 1

The query just returns the first row after n-1 row(s) so you get the n^th highest record.

For example, if you want to get the second most expensive product (n = 2) in the products table, you just use the following query:

SELECT productCode, productName, buyPrice FROM products  ORDER BY buyPrice desc  LIMIT 1, 1

Here is the result:

+-------------+--------------------------------+----------+ | productCode | productName                    | buyPrice | +-------------+--------------------------------+----------+ | S18_2238    | 1998 Chrysler Plymouth Prowler | 101.51 | +-------------+--------------------------------+----------+ 1 row in set (0.00 sec)

The second technique to get the Nth highest record is using SQL subquery:

SELECT * FROM table_name AS a  WHERE n - 1 = ( SELECT COUNT(primary_key_column)  FROM products b  WHERE  b.column_name > a. column_name)

You can achieve the same result using the first technique to get the second most expensive product as the following query:

SELECT productCode, productName, buyPrice FROM products a  WHERE 1 = (  SELECT COUNT(productCode)  FROM products b  WHERE b.buyPrice > a.buyPrice)

In this tutorial, we have shown you how to select the n^th record in a database table using LIMIT clause in MySQL.

原文链接：http://outofmemory.cn/mysql/tips/select-nth-highest-record-database-table-using-mysql