CODEWITHSUNDEEP
sqlsql-server
M.sid
M.sid

Reputation: 3

Write a SQL query to find employees who earn the top three salaries for the IT department

Write a SQL query to find employees who earn the top three salaries for the IT department.

My query:

SELECT TOP(3) WITH TIES 
    d.name AS department, e.name AS employee, e.salary 
FROM 
    employee e, department d
WHERE 
    e.departmentid = d.id AND d.name ='IT'
ORDER BY 
    e.salary DESC

This is supposed to show me 4 results. Because I'm including the ties. But for some reason it is not. I don't understand why. I need the top 3 salaries including the ties.

I get this result:

Department Employee Salary
IT Max 90000
IT Randy 85000
IT Joe 85000

Expected results:

Department Employee Salary
IT Max 90000
IT Randy 85000
IT Joe 85000
IT Will 70000

Employee table

Id Name Salary DepartmentId
1 Joe 85000 1
2 Henry 80000 2
3 Sam 60000 2
4 Max 90000 1
5 Janet 69000 1
6 Randy 85000 1
7 Will 70000 1

Department table:

Id Name
1 IT
2 Sales

Upvotes: 0

Views: 696

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270473

You are going to need window functions for this. SELECT TOP (n) WITH TIES stops at row n and only includes more rows if there are ties.

So, in addition to proper, explicit, standard, readable JOIN syntax, you can learn about window functions:

SELECT e.*
FROM (SELECT d.name as department, e.name as employee, e.salary,
             DENSE_RANK() OVER (ORDER BY e.salary DESC) as seqnum
      FROM employee e JOIN
           department d
           ON e.departmentid = d.id and d.name = 'IT'
     ) e
WHERE seqnum <= 3
ORDER BY e.salary DESC

Upvotes: 1

Related Questions