How To Select The nth Highest Record In MySQL

Summary: in this tutorial, you will learn how to select the n^th 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 n^th highest record. For example, the get the second-most expensive product from the products table.

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.

The following is the query to get the n^th 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 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;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 n^th highest record.

Get the n^th 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)

Try It Out

Here is the result:

The second technique to get the n^th 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)

Try It Out

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