# PostgreSQL Subquery: Get three maximum salaries

**19.** Write a query to get three maximum salaries.

**Sample Solution:**

**Code:**

```
-- This SQL query retrieves distinct salary values where each salary is in the top 3 distinct salary values in the employees table, ordered by salary in descending order.
SELECT DISTINCT salary -- Selects distinct salary values from the employees table (aliased as 'a')
FROM employees a -- Specifies the table from which to retrieve data (aliased as 'a'), in this case, the employees table
WHERE 3 >= ( -- Filters the rows to include only those where the count of distinct salary values that are less than or equal to the salary of the current row is equal to 3 or fewer
SELECT COUNT(DISTINCT salary) -- Subquery: Counts the distinct salary values that are less than or equal to the salary of the current row
FROM employees b -- Specifies the table from which to retrieve data (aliased as 'b'), in this case, the employees table
WHERE a.salary <= b.salary -- Filters distinct salary values that are less than or equal to the salary of the current row (aliased as 'a')
)
ORDER BY a.salary DESC; -- Orders the result set by salary in descending order
```

**Explanation:**

- This SQL query retrieves distinct salary values where each salary is in the top 3 distinct salary values in the employees table, ordered by salary in descending order.
- The outermost SELECT statement retrieves distinct salary values from the employees table (aliased as 'a').<./
- The WHERE clause filters the rows to include only those where the count of distinct salary values that are less than or equal to the salary of the current row is equal to 3 or fewer.
- The subquery calculates the count of distinct salary values that are less than or equal to the salary of the current row.
- The ORDER BY clause orders the result set by salary in descending order.

**Sample table: employees**

Output:

pg_exercises=# SELECT DISTINCT salary pg_exercises-# FROM employees a pg_exercises-# WHERE 3 >= (SELECT COUNT(DISTINCT salary) pg_exercises(# FROM employees b pg_exercises(# WHERE a.salary <= b.salary) pg_exercises-# ORDER BY a.salary DESC; salary ---------- 24000.00 17000.00 14000.00 (3 rows)

## Practice Online

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

**Previous: ** Write a subquery to display the information for all the departments where no employee is working.

**Next: ** Write a query to get three minimum salaries.

**What is the difficulty level of this exercise?**

**Weekly Trends and Language Statistics**- Weekly Trends and Language Statistics