MySQL get nth highest paid and nth lowest paid salary

This is a most general asked question in a MySQL database interview to find the nth highest paid or nth lowest paid salary of the employee. There are several ways to get this, but in this article, we will use the easiest process to fetch the nth highest and nth lowest paid salary of the employee.

Suppose, we have the following employee salary records -

CREATE TABLE IF NOT EXISTS `empsalary` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(100) NOT NULL,
  `salary` int(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `empsalary` (`id`, `emp_name`, `salary`) VALUES
(1, 'Smith', 30000),
(2, 'Joney', 20000),
(3, 'Mariya', 40000),
(4, 'Zoya', 35000),
(5, 'Smith', 25000),
(6, 'Rosy', 75000);

+----+----------+--------+
| id | emp_name | salary |
+----+----------+--------+
|  1 | Smith    |  30000 |
|  2 | Joney    |  20000 |
|  3 | Mariya   |  40000 |
|  4 | Zoya     |  35000 |
|  5 | Smith    |  25000 |
|  6 | Rosy     |  75000 |
+----+----------+--------+

Method 1 : Use MySQL SELECT Subquery

Here is the SELECT subquery method to get the nth highest paid salary of the employee -

SELECT * FROM empsalary s1
WHERE N-1 = 
(SELECT COUNT(DISTINCT salary) 
FROM empsalary s2 
WHERE s2.salary > s1.salary)

If you want to get the 3rd highest paid salary of the employee (N = 3), put (N-1) i.e, 2 in WHERE clause-

SELECT * FROM empsalary s1
WHERE 2 = 
(SELECT COUNT(DISTINCT salary) 
FROM empsalary s2 
WHERE s2.salary > s1.salary)

It returns the following output -

+----+----------+--------+
| id | emp_name | salary |
+----+----------+--------+
|  4 | Zoya     |  35000 |
+----+----------+--------+

Here is the SELECT subquery method to get the nth lowest paid salary of employee, suppose we want to fetch 3rd lowest paid salary, i.e, (N = 3)

SELECT * FROM empsalary s1
WHERE 2 = 
(SELECT COUNT(DISTINCT salary) 
FROM empsalary s2 
WHERE s2.salary < s1.salary)

It returns the following output -

+----+----------+--------+
| id | emp_name | salary |
+----+----------+--------+
|  1 | Smith    |  30000 |
+----+----------+--------+

Method 2 : Use MySQL SELECT LIMIT

The MySQL LIMIT keyword is used to fetch the limited result rows. So, by using this we can easily get the nth highest or nth lowest paid salary of the employee. Here, we have found the 3rd highest paid salary and taken N = 3, i.e., LIMIT 2 (N-1).

SELECT * FROM empsalary ORDER BY salary DESC LIMIT 2, 1

It returns the following output -

+----+----------+--------+
| id | emp_name | salary |
+----+----------+--------+
|  4 | Zoya     |  35000 |
+----+----------+--------+

similarly, the given statement returns the 3rd lowest paid salary of the employee -

SELECT * FROM empsalary ORDER BY salary ASC LIMIT 2, 1

It returns the following output -

+----+----------+--------+
| id | emp_name | salary |
+----+----------+--------+
|  1 | Smith    |  30000 |
+----+----------+--------+

Related MySQL Exercises

MySQL concatenate
MySQL Get Current Date, User, Version
MySQL Get Maximum Value
MySQL Administrator
MySQL where clause
MySQL order by
MySQL get difference between two dates
MySQL Pattern Matching
MySQL Join
MySQL Regular Expression
MySQL delete duplicate row
MySQL update multiple rows
Display the nth row from MySQL
Commit and rollback in mysql
MySQL SELECT top 5
MySQL display the alternate rows