w3resource

MySQL Subquery Exercises: Find the 4th minimum salary in the employees table

MySQL Subquery: Exercise-17 with Solution

Write a MySQL query to find the 4th minimum salary in the employees table.

Sample table: employees


Code:

-- Selecting distinct salaries from the employees table for which there are exactly 4 distinct salaries less than or equal to it
SELECT DISTINCT salary 
-- Selecting data from the employees table, aliasing it as 'e1'
FROM employees e1 
-- Filtering the result set to include only those salaries for which there are exactly 4 distinct salaries less than or equal to it
WHERE 4 = 
    -- Subquery to count the number of distinct salaries less than or equal to each salary
    (SELECT COUNT(DISTINCT salary) 
    -- Selecting data from the employees table, aliasing it as 'e2'
    FROM employees e2 
    -- Filtering the result set to include only distinct salaries less than or equal to the salary in the outer query (e1.salary)
    WHERE e2.salary <= e1.salary);

Explanation:

  • This MySQL code selects distinct salary values from the "employees" table.
  • It filters the result set to include only those salary values for which there are exactly 4 distinct salary values less than or equal to it.
  • This is achieved using a subquery where the number of distinct salaries less than or equal to each salary is counted. The outer query compares this count with the constant value 4.
  • If there are exactly 4 distinct salaries less than or equal to a particular salary, it will be included in the result set.

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: Find the 4th minimum salary in the employees table

 

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 find the 5th maximum salary in the employees table.
Next:Write a MySQL query to select last 10 records from a table.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.