CODEWITHSUNDEEP
sqloracle-databasegroup-byaggregate-functionsgreatest-n-per-group
user3719859
user3719859

Reputation: 35

SQL - get MIN value of row and check this MIN value to be in row at least 2 times

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

Upvotes: 1

Views: 39

Answers (3)

Jcis
Jcis

Reputation: 173

    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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269943

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.

Upvotes: 1

GMB
GMB

Reputation: 222482

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

Upvotes: 1

Related Questions