Summary: in this tutorial, you will learn how to select the nth 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 nth highest record, you need to perform the following steps:
First you get the n highest records and sort them in ascending order. The nth 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 nth 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 nth 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 nth record in a database table using LIMIT clause in MySQL.
原文链接：http://outofmemory.cn/mysql/tips/select-nth-highest-record-database-table-using-mysql