login

Subscribe to MySQL Tutorial website RSS Feed
 » MySQL Tips » Select the nth Highest Record in a Database Table using MySQL

Select the nth Highest Record in a Database Table

In this tutorial, you will learn how to select the Nth highest record in a database table by using various techniques. These techniques are very useful, for instance you may want to see the product which is second most expensive in Products database table.
 

The first idea is we get the Nth highest result set and sort them in ascending order. The Nth highest record is the last record in the result set. And then we order the result set in descending order and get the first one. Here is SQL script to accomplish it:
 

Here is the query to get the Nth highest result set in the ascending order:

SELECT * 
FROM table_name
ORDER BY column_name ASC
LIMIT n

Query to get the nth highest record:

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

MySQL provides us the LIMIT clause so we just have to leverage it 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 Products database table, you just need to perform 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)

We can achieve the same result as the first technique on Products table to get the second most expensive product by performing the following query:

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