What I'm trying to achieve is this:
1) Get the minimum value of a salary in the table for every department.
2) If this minimum value exists in the table at least two times for every department, then show its department id.
Example:
column1 name salary department_id
id1 John1 10000 1
id2 John2 10000 1
id3 John3 30000 2
id4 John4 30000 2
id5 John5 50000 3
id6 John6 20000 4
Result:
department_id
1
2
If I followed you correctly, you want departments where more than one employee has the lowest salary.
Here is an approach using window functions, which works by comparing row_number() and rank():
select distinct department_id
from (
select
t.*,
row_number() over(partition by department_id order by salary) rn,
rank() over(partition by department_id order by salary) rnk
from mytable t
) t
where rnk = 1 and rn > 1
If I understand correctly, you want the departments where the minimum salary occurs at least twice. This makes me think window functions:
select t.department_id
from (select t.*,
count(*) over (partition by department_id, salary) as cnt,
row_number() over (partition by department_id order by salary) as seqnum
from t
) t
where seqnum = 1 and cnt > 1;
Note that you don't need a select distinct, because this chooses at most one row per department.
SELECT department_id
FROM Employee
WHERE Employee.salary = (select min(emp.salary) from Employee emp where emp.department_id = Employee.department_id)
GROUP BY department_id
HAVING COUNT(1) >=2
