Top Salaries per Department
The employees table lists who works where and what they earn.
Return the employees who have one of the two highest distinct salaries in their department. People who earn the same amount share a rank, so a department can return more than two people. Departments with fewer than two distinct salaries return everyone.
Return department, name and salary, ordered by department, then salary from high to low, then name.
Tables
CREATE TABLE employees ( id serial PRIMARY KEY, name text NOT NULL, department text NOT NULL, salary integer NOT NULL );
Example data
INSERT INTO employees (name, department, salary) VALUES ('Asha','Engineering',9500),('Ravi','Engineering',9000),('Kim','Engineering',8000),('Maya','Sales',7000),('Omar','Sales',6500);Your query is also checked on hidden datasets with different data.
Hints
Similar problems
- Sales Performance: Rank by Consecutive UpswingHard
- Student Course Ranking by SemesterMedium
- Rank Students by Cumulative Score in Each CollegeMedium
- Delivery Performance RankingMedium
- Rank Student Scores in Each SubjectMedium
- Cricket Performance: Consecutive Matches Above AverageHard
More in: SQL and Databases problems
-- Employees with one of the two highest distinct salaries in their department
SELECT department, name, salary
FROM employees;
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.