Analytic functions and plain SQL equivalent-CodePudding

I'm using Oracle and SQL Developer. I have downloaded HR schema and need to do some queries with it. Now I'm working with table Employees. As an user I need to see the list of employees with lowest salary in each department. I need to provide different solutions by means of plain SQL and one of analytic functions. About analytic functions, I have used RANK():

SELECT *
FROM
    (SELECT
         employee_id,
         first_name,
         department_id,
         salary,
         RANK() OVER (PARTITION BY department_id 
                      ORDER BY salary) result
     FROM
         employees)
WHERE 
    result = 1 
    AND department_id IS NOT NULL;

The result seems correct:

but when I try to use plain SQL I actually get all employees with their salaries.

Here is my attempt with GROUP BY:

SELECT 
    department_id, MIN(salary) AS "Lowest salary"
FROM 
    employees
GROUP BY 
    department_id;

This code seems good, but I need to also get columns first_name and employee_id.

I tried to do something like this:

SELECT
    employee_id,
    first_name,
    department_id,
    MIN(salary) result
FROM
    employees
GROUP BY
    employee_id,
    first_name,
    department_id;

and this:

SELECT
    employee_id,
    first_name,
    salary,
    departments.department_id
FROM
    employees
LEFT OUTER JOIN 
    departments ON (employees.department_id = departments.department_id)
WHERE
    employees.salary = (SELECT MIN(salary)
                        FROM departments 
                        WHERE department_id = employees.department_id)

These seem wrong. How can I change or modify my queries to get the same result as when I'm using RANK() by means of plain SQL (two solutions at least)?

CodePudding user response：

One of the options could be like here:

SELECT EMPNO, ENAME, DEPTNO, SAL
FROM   EMP
WHERE  SAL IN (Select MIN_SAL From (SELECT DEPTNO, Min(SAL) "MIN_SAL"
                                    FROM   EMP
                                    GROUP BY DEPTNO))
ORDER BY deptno, sal;

CodePudding user response：

You can use a subquery to find the lowest salary per employee and use the main query to only show the information of those employees that are selected by this subquery:

SELECT 
employee_id,
first_name,
department_id,
salary
FROM employees e1
WHERE salary =
(SELECT MIN(e2.salary)
FROM employees e2
WHERE e1.employee_id = e2.employee_id);

This will produce exactly the same outcome as your query. I think it would make sense to apply some sorting which is missing in your query. I don't know how you want to sort, but here an example to sort by the employee's name:

SELECT 
employee_id,
first_name,
department_id,
salary
FROM employees e1
WHERE salary =
(SELECT MIN(e2.salary)
FROM employees e2
WHERE e1.employee_id = e2.employee_id)
ORDER BY first_name;