Q:

MySQL get nth highest paid and nth lowest paid salary

belongs to collection: MySQL Exercises

YasminRadwan26

Save to bookmark

|

2022-05-14

|

174

0

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 exercise, 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 |
+----+----------+--------+

Sql programming,

login and add your own answer to this question

All Answers

YasminRadwan26

2022-05-14

need an explanation for this answer? contact us directly to get an explanation for this answer

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 |
+----+----------+--------+

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Display the 3rd row from the MySQL 'empdata&#... >>

<< MySQL update multiple rows in one query...