PostgreSQL Subquery: Get nth maximum of salaries

21. Write a query to get nth max salaries of employees.

Sample Solution:

Code:

SELECT * 
FROM employees emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(emp2.salary))
FROM employees emp2
WHERE emp2.salary > emp1.salary);

Sample table: employees

Output:

pg_exercises=# SELECT *
pg_exercises-# FROM employees emp1
pg_exercises-# WHERE (1) = (
pg_exercises(# SELECT COUNT(DISTINCT(emp2.salary))
pg_exercises(# FROM employees emp2
pg_exercises(# WHERE emp2.salary > emp1.salary);

 employee_id | first_name | last_name |     email     | phone_number | hire_date  | job_id |  salary  | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+--------+----------+----------------+------------+---------------
         101 | Neena      | Kochhar   | not available | 515.123.4568 | 1987-06-18 | AD_VP  | 17000.00 |           0.00 |        100 |            90
         102 | Lex        | De Haan   | not available | 515.123.4569 | 1987-06-19 | AD_VP  | 17000.00 |           0.00 |        100 |            90
(2 rows)

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to get three minimum salaries.
Next: PostgreSQL Exercises, Practice, Solution