How to select nth Highest Record in MySQL
In this section, we are going to learn how we can select the nth highest record in a database table with the help of various techniques.
We can get the maximum (highest) or minimum (lowest) record in the database table very easily by using the MAX() or MIN() function. But suppose we want to get the nth highest record from the table (for example, get the second-most expensive salary from the employee table). In that case, there is no function available to find it quickly, which makes it complicated.
By performing the following steps, we can select the nth highest record in a MySQL database table:
1. The first step is to sort the desired column in ascending order to get the n highest records, which is the last record in the resultant output. See the below query:
2. After that, we need to sort the resultant output in descending order and get the first record.
The above query can also be rewritten by using the LIMIT clause that constrains the number of rows in the resultant output as follows:
This query will return the first row after the n-1 rows that should be the nth highest record.
Example:
Let us understand how to get the nth highest record from the table with the help of an example. First, we will create an Employee table using the below query:
Next, insert records using the below query:
Execute the SELECT statement to verify the record:
Suppose we want to get the second highest salary of an employee (n = 2) in the Employee table; we can use the below statement:
We will see the output as follows:
Suppose we want to get the third-highest salary of an employee (n = 3) in the Employee table; we can use the below statement:
We will see the output as follows:
Get the nth highest record using a subquery
We can also get the nth highest record with the help of subquery, which depends upon the main query and processed for every record returned by the main query. This technique is rarely used because of its slow performance/execution speed.
See the below query that returns nth highest record using the subquery:
See the below query that returns the second highest salary from the employee table using the subquery:
We will get the same output returned by the previous query:
