w3resource

MySQL Subquery Exercises: Query to get 3 maximum salaries

MySQL Subquery: Exercise-20 with Solution

Write a MySQL query to get 3 maximum salaries.

Sample table: employees


Code:

-- Selecting distinct salary values from the employees table 
SELECT DISTINCT salary 
-- Selecting data from the employees table, aliasing it as 'a'
FROM employees a 
-- Filtering the result set to include only salary values where there are at most 3 distinct salary values greater than or equal to it
WHERE 3 >= 
    -- Subquery to count the number of distinct salaries greater than or equal to each salary
    (SELECT COUNT(DISTINCT salary) 
    -- Selecting data from the employees table, aliasing it as 'b'
    FROM employees b 
    -- Filtering the result set to include only distinct salaries greater than or equal to the salary in the outer query (a.salary)
    WHERE b.salary >= a.salary) 
-- Sorting the result set in descending order based on salary
ORDER BY a.salary DESC;

Explanation :

  • This MySQL code selects distinct salary values from the "employees" table.
  • It filters the results to only include salary values where there are at most 3 distinct salary values greater than or equal to it.
  • This is achieved using a subquery where the number of distinct salaries greater than or equal to each salary is counted. The outer query compares this count with the constant value 3.
  • The result set is then sorted in descending order based on salary using the ORDER BY clause.

MySQL Subquery Syntax:

MySQL subquery syntax

- The subquery (inner query) executes once before the main query (outer query) executes.
- The main query (outer query) use the subquery result.

MySQL SubQueries: Query to get 3 maximum salaries.

 

MySQL Code Editor:

Structure of 'hr' database:

hr database

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

Previous:Write a MySQL query to list the department ID and name of all the departments where no employee is working.
Next:Write a MySQL query to get 3 minimum salaries.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.