select distinct *
from t t1
where 5 >= (
select count ( distinct t2.sal )
from t t2
where t2.deptno = t1.deptno
and t2.sal >= t1.sal
);
I'll be grateful if you can explain. how the number work ,5, without variable and why we are using t2.sal >=t1.sal here. I'm confused could pls explain it to me.
Why 5? I dunno - you'll have to ask the original author for that one!
As to what this query is doing...
It's returning a list of all the rows where:
- For the same deptno
- There are five or less different values for sal that are the same or more than the sal for the current row
Plugging in employees, the question it's answering is:
Which employees have five or less salaries greater than or equal to their salary in their department?
Or to put it another way:
Which employees earn one of the top five salaries in their department?
Which gives this query:
select employee_id, salary, department_id
from hr.employees t1
where 5 >= (
select count( distinct t2.salary )
from hr.employees t2
where t2.department_id = t1.department_id
and t2.salary >= t1.salary
);
EMPLOYEE_ID SALARY DEPARTMENT_ID
100 24000 90
101 17000 90
102 17000 90
103 9000 60
104 6000 60
105 4800 60
106 4800 60
107 4200 60
108 12008 100
109 9000 100
110 8200 100
111 7700 100
112 7800 100
114 11000 30
115 3100 30
116 2900 30
117 2800 30
118 2600 30
120 8000 50
121 8200 50
122 7900 50
123 6500 50
124 5800 50
145 14000 80
146 13500 80
147 12000 80
148 11000 80
168 11500 80
174 11000 80
178 7000 <null>
200 4400 10
201 13000 20
202 6000 20
203 6500 40
204 10000 70
205 12008 110
206 8300 110
There's no need for the second query against employees. With dense_rank you can find the top five salaries per department.
Which gives a query like:
select employee_id, salary, department_id
from (
select employee_id, salary, department_id ,
dense_rank () over (
partition by department_id
order by salary desc
) dr
from hr.employees
)
where dr <= 5;