PostgreSQL Subquery: Get nth maximum of salaries

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

Sample Solution:

Code:

-- This SQL query retrieves records from the employees table where each employee's salary is the highest among all distinct salary values in the employees table.

SELECT * -- Selects all columns from the employees table (aliased as 'emp1')
FROM employees emp1 -- Specifies the table from which to retrieve data (aliased as 'emp1'), in this case, the employees table
WHERE (1) = ( -- Filters the rows to include only those where the count of distinct salary values greater than the salary of the current row is equal to 1
    SELECT COUNT(DISTINCT(emp2.salary)) -- Subquery: Counts the distinct salary values greater than the salary of the current row
    FROM employees emp2 -- Specifies the table from which to retrieve data (aliased as 'emp2'), in this case, the employees table
    WHERE emp2.salary > emp1.salary -- Filters distinct salary values greater than the salary of the current row (aliased as 'emp1')
);

Explanation:

• This SQL query retrieves records from the employees table where each employee's salary is the highest among all distinct salary values in the employees table.
• The outermost SELECT statement retrieves all columns from the employees table (aliased as 'emp1').
• The WHERE clause filters the rows to include only those where the count of distinct salary values greater than the salary of the current row is equal to 1.
• The subquery calculates the count of distinct salary values greater than the salary of the current row.

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