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:
- The subquery (inner query) executes once before the main query (outer query) executes.
- The main query (outer query) use the subquery result.
MySQL Code Editor:
Structure of '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?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics