SQL Exercises: Employees who earn second lowest salary of all

SQL SUBQUERY: Exercise-52 with Solution

From the following table, write a SQL query to find those employees who earn the second-lowest salary of all the employees. Return all the fields of employees .

Sample table: employees

Sample Solution:

SELECT *
FROM employees m
WHERE  2 = (SELECT COUNT(DISTINCT salary ) 
            FROM employees
            WHERE  salary <= m.salary);

Sample Output:

employee_id	first_name	last_name	email	phone_number 	hire_date  job_id	salary	commission_pct	manager_id	department_id
128		Steven		Markle		SMARKLE	  650.124.1434	2008-03-08ST_CLERK 	2200.00		0.00	120	 	50
136		Hazel		Philtanker	HPHILTAN  650.127.1634	2008-02-06ST_CLERK 	2200.00		0.00	122		50

Code Explanation:

The said query in SQL that selects all employees who have the second-lowest distinct salary in the employees table.
A subquery is used to count the number of distinct salaries that are less than or equal to each employee's salary in the outer query, based on the data in the subquery. Exactly two distinct salaries will be less than or equal to the second lowest salary of an employee. Therefore, the outer query selects all employees for whom the subquery returns a count of 2.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Employees worked as a Sales Representative in the past.
Next SQL Exercise: Display the details of departments managed by Susan.