How to get only one record for the minimum wage employee from each department? DB2

Question

I barely just started on database languages, so I do not know anything about JOIN, PARTITION, ETC. but this is what I have so far.

What I tried:

SELECT MIN(SALARY) AS "MIN SALARY", WORKING.DID, ENAME
  FROM DEPARTMENT, EMPLOYEE, WORKING
 WHERE WORKING.EID = EMPLOYEE.EID
 GROUP BY WORKING.DID, ENAME;

Result:
 MIN SALARY DID ENAME
 ---------- --- ------
      10000 101 Dustin
      15000 102 Bob
      20000 102 David
      10000 102 Dustin
      10000 103 Alex
       8000 103 Alice
       7000 103 Mike

What I want:

 MIN SALARY DID ENAME
 ---------- --- ------
      10000 101 Dustin
      10000 102 Dustin
       7000 103 Mike

Table structures:

CREATE TABLE EMPLOYEE (
    EID INTEGER NOT NULL,
    ENAME VARCHAR(25),
    SALARY DECIMAL,
    PRIMARY KEY (EID)
);

CREATE TABLE WORKING (
    EID INTEGER NOT NULL,
    DID INTEGER NOT NULL,
    STIME DATE,
    FOREIGN KEY (EID) REFERENCES EMPLOYEE (EID),
    FOREIGN KEY (DID) REFERENCES DEPARTMENT (DID),
    PRIMARY KEY (EID, DID)
);

CREATE TABLE DEPARTMENT (
    DID INTEGER NOT NULL,
    DNAME VARCHAR(10),
    DADDRESS VARCHAR(20),
    PRIMARY KEY (DID)
);

ER Diagram

You select from three tables which is a three table join but you just have one join condition WORKING.EID = EMPLOYEE.EID - so it is a cartesian join with the department table - not a good idea generally. Show the table structures — MichaelTiefenbacher, Feb 22 '20 at 20:24
You should always specify a table alias for all participating columns to not make others guessing. It’s regarding SALARY and ENAME columns. What if multiple employees of the same department have the minimum wage in the department? Do you want to have all of them in the output or just some arbitrary one of them? — Mark Barinstein, Feb 22 '20 at 20:33
Just updated the post. MichaelTiefenbacher - I am not sure what is a cartesian join. @MarkBarinstein I suppose I would like all of them. — Martin Tran, Feb 22 '20 at 22:04

Barbaros Özhan · Accepted Answer · 2020-02-23T09:23:39.290

You can prefer using online analytical processing(OLAP) functions such as DENSE_RANK() to get the desired result easily

SELECT Q.SALARY, Q.DID, Q.ENAME 
  FROM
  (
   SELECT E.SALARY, W.DID, E.ENAME,
          DENSE_RANK() OVER (PARTITION BY W.DID ORDER BY E.SALARY) AS DR
     FROM WORKING W
     JOIN DEPARTMENT D
       ON D.DID = W.EID
     JOIN EMPLOYEE E
       ON E.EID = W.EID
   ) Q
  WHERE DR = 1

in which explicit JOIN syntax, the syntax having comma-seperated tables is considered as old-deprecated one , is used. Using (INNER)JOIN is enough for your case, whereas there may be cases needing OUTER (LEFT-RIGHT or FULL) JOINs.

Within an analytic function, PARTITION Clause is used to express the GROUPING BY criteira. Based on the ascending ORDER for the salary, we'll get the minimum ones to be filtered out through the results coming from the DENSE_RANK() function with values equal to one.

By using this method, you can get all the employees with the minimum salaries even there are more than one person for each department.

Moreover, better to alias the tables by their name's first character(or by two or three chars contained within their names depending on the common chars. for the first letters) in order to qualify the columns within the query rather than using whole names.

score 0 · Answer 2 · answered Feb 23 '20 at 09:31

There is no need to use the DEPARTMENT table in the statement.
While using OLAP functions is preferable way and generally provides better performance, there is another method not using OLAP functions based on rejoining base tables to a sub-select with grouped result, where we get department minimum salary. We rejoin these department minimums back to WORKING & EMPLOYEE picking up employees with the department minimum salary only.

SELECT G.DID, G.SALARY, E.ENAME
FROM
(
SELECT W.DID, MIN(E.SALARY) SALARY
FROM WORKING W
JOIN EMPLOYEE E ON E.EID = W.EID
GROUP BY W.DID
) G
JOIN WORKING  W ON W.DID = G.DID
JOIN EMPLOYEE E ON E.EID = W.EID AND E.SALARY = G.SALARY;

How to get only one record for the minimum wage employee from each department? DB2

2 Answers2