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 in various techniques. These techniques are very useful, for instance you may want to see the product which has second most expensive in Products database table.
The first idea is we get the nth highest record 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:
Get the Nth highest result set in ascending order:

SELECT * FROM table_name ORDER BY column_name AS C LIMIT n

Get the nth highest record:

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

Luckily, with LIMIT clause you can 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 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 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)

And we 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)