Select nth Highest Record in MySQL

In this tutorial, we will learn how to select Nth highest record in MySQL using various methods. The main objective is to find a way of selecting the nth record based on specific criteria. Let’s get started!

Introduction to Selecting the nth Highest Record

In MySQL, it is easy to find the highest and lowest record but to find the n^th highest record, is somewhat different. To select the n^th highest record, the steps are:

• First of all, arrange the n highest records and sort them in ascending order.
• The last record in the result set is the n^th highest record.
• And when we sort the result set in descending order, then we get the first one.

The syntax for the n^th highest records in the ascending order is-

Select *
From
    table_name
Order By column_name ASC
LIMIT N;Code language: SQL (Structured Query Language) (sql)

The query for the same is-

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)

Here, using the LIMIT clause we have limited the number of rows in the result set. If we replace LIMIT N with LIMIT N-1,1 then it returns the first row after N-1 rows.

Example of selecting the nth record

Here we will use the classicmodels database and in the database, we will use the payments table. Let us find the second highest payment. So N=2 will be used. The code for the following is:

Select
    amount, paymentDate, customerNumber
From
    payments
Order By amount DESC
LIMIT 1 , 1;Code language: SQL (Structured Query Language) (sql)

Output-

Another way to get the n^th highest record is by using MySQL subquery. The syntax for the following is-

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)

Let us find the second-highest amount in the payments table. The code for the following is-

Select
    amount, paymentDate, customerNumber
From
    payments a
Where
    1 = (Select 
            Count(customerNumber)
        From
            payments b
        Where
            b.amount > a.amount);Code language: SQL (Structured Query Language) (sql)

Output-

Conclusion

In this tutorial, we learned how to select Nth highest record in the table using the LIMIT clause. We also learned how to sort the records using ORDER BY clause. If you have any questions, please feel free to ask in the comment section below. Thanks for reading!