How to find nth highest value of a MySQL column?

To find the nth highest value of a column, you need to use ORDER BY DESC with LIMIT clause. If you want the second highest value of a column, use the below syntax:

SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 1,1;

If you want the fourth highest value of a column, use the below syntax:

SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 3,1;

If you want the first highest value of a column, use the below syntax:

SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 1;

As discussed in the above syntax, you need to change only in LIMIT clause. To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table NthSalaryDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(10),
   -> Salary int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (1.03 sec)

Insert some records in the table using insert command. The query is as follows:

mysql> insert into NthSalaryDemo(Name,Salary) values('Larry',5700);
Query OK, 1 row affected (0.41 sec)
mysql> insert into NthSalaryDemo(Name,Salary) values('Sam',6000);
Query OK, 1 row affected (0.16 sec)
mysql> insert into NthSalaryDemo(Name,Salary) values('Mike',5800);
Query OK, 1 row affected (0.16 sec)
mysql> insert into NthSalaryDemo(Name,Salary) values('Carol',4500);
Query OK, 1 row affected (0.17 sec)
mysql> insert into NthSalaryDemo(Name,Salary) values('Bob',4900);
Query OK, 1 row affected (0.20 sec)
mysql> insert into NthSalaryDemo(Name,Salary) values('David',5400);
Query OK, 1 row affected (0.27 sec)
mysql> insert into NthSalaryDemo(Name,Salary) values('Maxwell',5300);
Query OK, 1 row affected (0.21 sec)
mysql> insert into NthSalaryDemo(Name,Salary) values('James',4000);
Query OK, 1 row affected (0.19 sec)
mysql> insert into NthSalaryDemo(Name,Salary) values('Robert',4600);
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from NthSalaryDemo;

The following is the output:

+----+---------+--------+
| Id | Name    | Salary |
+----+---------+--------+
|  1 | Larry   |   5700 |
|  2 | Sam     |   6000 |
|  3 | Mike    |   5800 |
|  4 | Carol   |   4500 |
|  5 | Bob     |   4900 |
|  6 | David   |   5400 |
|  7 | Maxwell |   5300 |
|  8 | James   |   4000 |
|  9 | Robert  |   4600 |
+----+---------+--------+
9 rows in set (0.00 sec)

Case 1: Here is the query to get the nth highest value of a column.

The below query will give the fourth highest value of a column ‘Salary’:

mysql> select *from NthSalaryDemo order by Salary desc limit 3,1;

The following is the output:

+----+-------+--------+
| Id | Name  | Salary |
+----+-------+--------+
|  6 | David | 5400   |
+----+-------+--------+
1 row in set (0.00 sec)

Case 2: Here is the query to get the second highest value of a column ‘Salary’:

mysql> select *from NthSalaryDemo order by Salary desc limit 1,1;

The following is the output:

+----+------+--------+
| Id | Name | Salary |
+----+------+--------+
|  3 | Mike | 5800   |
+----+------+--------+
1 row in set (0.00 sec)

Case 3: Here is the query to get the first highest value of a column:

mysql> select *from NthSalaryDemo order by Salary desc limit 1;

The following is the output:

+----+------+--------+
| Id | Name | Salary |
+----+------+--------+
|  2 | Sam  | 6000   |
+----+------+--------+
1 row in set (0.00 sec)

Case 4: If you want to get the 8th highest value of a column ‘Salary’, use the following query:

mysql> select *from NthSalaryDemo order by Salary desc limit 7,1;

The following is the output:

+----+-------+--------+
| Id | Name  | Salary |
+----+-------+--------+
|  4 | Carol |   4500 |
+----+-------+--------+
1 row in set (0.00 sec)