Select nth Highest Record in MySQL

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 nth highest record, is somewhat different. To select the nth 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 nth highest record.
  • And when we sort the result set in descending order, then we get the first one.

The syntax for the nth 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-

Second Highest Amount
Second Highest Amount

Another way to get the nth 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-

Amount Second Highest
Amount Second Highest

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!