CODEWITHSUNDEEP
sqloracle-databasegroup-bygreatest-n-per-group
rdevenz
rdevenz

Reputation: 29

query that for each position bring the id of the employee with the lowest salary

need to create a query in Oracle's SQL that for each JOB, return the employee ID with the lowest SALARY.

I tried like this, but it brought me all SALARIES and ID:

SELECT JOB, MIN(SAL) FROM EMP
    GROUP BY EMPNO, JOB;

And so it brings me only the lowest salary of all:

SELECT JOB, SAL 
    FROM SCOTT_EMP 
    WHERE SAL = (SELECT MIN(SAL) FROM EMP);

Can you help me?

Upvotes: 1

Views: 113

Answers (2)

Martin
Martin

Reputation: 2414

If I understand this correctly, you want to show the data of the employee with the lowest salary for each given occupation, correct? In this case, we can use GROUP BY to group by our occupations, and ORDER BY to order by our salaries.

Sample data example:

CREATE TABLE tbl_employees(id int(11), name varchar(100), occupation varchar(100));
CREATE TABLE tbl_salary(id int(11), salary decimal);

INSERT INTO tbl_employees VALUES(1, "John Doe", "Accountant");
INSERT INTO tbl_employees VALUES(2, "Christopher Stark", "Accountant");
INSERT INTO tbl_employees VALUES(3, "Arnold  Sanders", "Accountant");
INSERT INTO tbl_employees VALUES(4, "Jane Doe", "Marketer");
INSERT INTO tbl_employees VALUES(5, "Christina Lavine", "Marketer");
INSERT INTO tbl_employees VALUES(6, "Penny Lane", "Marketer");
INSERT INTO tbl_employees VALUES(7, "George Franklin", "Customer Support");

INSERT INTO tbl_employees VALUES(8, "Kent Brockmann", "Customer Support");
INSERT INTO tbl_employees VALUES(9, "Richard Felenski", "Customer Support");
INSERT INTO tbl_employees VALUES(10, "Patrick Neil", "Technical Support");
INSERT INTO tbl_employees VALUES(11, "Paul Richards", "Technical Support");
INSERT INTO tbl_employees VALUES(12, "Tom Harris", "Technical Support");
INSERT INTO tbl_employees VALUES(13, "Rebecca Moore", "Project Manager");
INSERT INTO tbl_employees VALUES(14, "Laura Cross", "Project Manager");
INSERT INTO tbl_employees VALUES(15, "Denise McIntyre", "Project Manager");

INSERT INTO tbl_salary VALUES(1, 100.00);
INSERT INTO tbl_salary VALUES(2, 150.00);
INSERT INTO tbl_salary VALUES(3, 300.00);
INSERT INTO tbl_salary VALUES(4, 110.00);
INSERT INTO tbl_salary VALUES(5, 120.00);
INSERT INTO tbl_salary VALUES(6, 235.00);
INSERT INTO tbl_salary VALUES(7, 240.00);
INSERT INTO tbl_salary VALUES(8, 200.00);
INSERT INTO tbl_salary VALUES(9, 160.00);
INSERT INTO tbl_salary VALUES(10, 230.00);
INSERT INTO tbl_salary VALUES(11, 320.00);
INSERT INTO tbl_salary VALUES(12, 150.00);
INSERT INTO tbl_salary VALUES(13, 168.00);
INSERT INTO tbl_salary VALUES(14, 175.00);
INSERT INTO tbl_salary VALUES(15, 350.00);

The query:

SELECT
  tbl_employees.id as emp_id,
  tbl_employees.name as emp_name,
  tbl_employees.occupation as emp_pos,
  tbl_salary.salary as emp_salary
FROM
  tbl_employees
INNER JOIN
  tbl_salary ON tbl_employees.id = tbl_salary.id
GROUP BY
  tbl_employees.occupation
ORDER BY
  tbl_salary.salary DESC

This should give you each employee with the lowest salary within each respectable occupation.

Output: enter image description here

Working fiddle here.

Upvotes: 0

GMB
GMB

Reputation: 222432

If you just want one more column, you could use the keep syntax:

select
    job,
    min(sal),
    min(id) keep(dense_rank first order by sal) id
from emp
group by job

For more columns, you can use window functions:

select *
from (
    select e.*, rank() over(partition by job order by sal) rn
    from emp e
) e
where rn = 1

Upvotes: 1

Related Questions