How To Select The nth Highest Record In MySQL
Summary: in this tutorial, you will learn how to select the nth highest record in a database table using various techniques.
It is easy to select the highest or lowest record in the database table with the MAX or MIN function. However, it’s a little bit tricky to select the nth highest record. For example, the get the second-most expensive product from the products table.
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.
The following is the query to get the nth highest records in the ascending order:
SELECT
*
FROM
table_name
ORDER BY column_name ASC
LIMIT N;Code language: SQL (Structured Query Language) (sql)
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;Code language: SQL (Structured Query Language) (sql)
Fortunately, MySQL provides us with the LIMIT clause that constrains the number of rows in the returned result set. You can rewrite the query above as the following query:
SELECT
*
FROM
table_name
ORDER BY column_name DESC
LIMIT n - 1, 1;Code language: SQL (Structured Query Language) (sql)
The query returns the first row after n-1 row(s) so you get the nth highest record.
Get the nth highest record example
For example, if you want to get the second most expensive product (n = 2) in the products table, you use the following query:
SELECT
productCode, productName, buyPrice
FROM
products
ORDER BY buyPrice DESC
LIMIT 1 , 1;Code language: SQL (Structured Query Language) (sql)
Here is the result:
The second technique to get the nth highest record is using MySQL 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)Code language: SQL (Structured Query Language) (sql)
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);Code language: SQL (Structured Query Language) (sql)
In this tutorial, we have shown you how to select the nth record in a database table using LIMIT clause in MySQL.