## Puzzle of the Week #20:

Produce the historical highest/lowest salary report that should comply with the following requirements:

• Use Single SELECT statement only
• Only employees who was paid the highest or lowest salary in their respective department at the moment of hiring should be selected
• Show name, date of hire, department number, job title, salary table (emp) columns and two additional calculated columns/flags: min_flag and max_flag to indicate that the employee was hired with the min/max salary in their respective department as of the time of hiring.
• If two or more employees in the same department are paid the same max/min salary, only the one who was hired first should be picked for the report.
• The query should work in Oracle 11g.

Comment: Apparently, the first employee in each department automatically qualifies for both, the lowest and the highest paid employee at the time of hiring.

### Expected Result:

A correct answer (and workarounds!) will be published here in a week.

### My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

### Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

### 9 thoughts on “Puzzle of the Week #20: Historical top/bottom paid employee report”

1. Taroon Ray August 11, 2016 / 4:10 am

SELECT ENAME, DEPTNO, JOB, HIREDATE, SAL,
CASE WHEN SAL = MXSAL THE 1 ELSE NULL END MAXFLAG,
CASE WHEN SAL = MNSAL THEN 1 ELSE NULL END MINFLAG
FROM
(

SELECT ENAME, DEPTNO, JOB, HIREDATE,
(
SELECT MAX(SAL) FROM EMP WHERE EMP.HIREDATE = E.HIREDATE
) MXSAL,
(
SELECT MIN(SAL) FROM EMP WHERE EMP.HIREDATE = E.HIREDATE
) MNSAL
FROM EMP E
);

2. Krishna Jamal August 11, 2016 / 10:29 am

WITH t AS (
SELECT Ename, Job, Deptno, HireDate, Sal,
MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mn_Sal,
MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mx_Sal
FROM Emp ),
t1 AS
(SELECT Deptno, Sal, MIN(HireDate) HireDate
FROM Emp
GROUP BY Deptno, Sal)
SELECT * FROM
(SELECT Ename, Job, Deptno, HireDate, Sal,
CASE WHEN Mn_Sal = Mx_Sal THEN 1
WHEN Mn_Sal = (SELECT MIN(t1.Sal)
FROM t1
WHERE t1.Deptno = t.Deptno
AND t1.HireDate = t.HireDate) THEN 1 END Min_Flag,
CASE WHEN Mn_Sal = Mx_Sal THEN 1
WHEN Mx_Sal = (SELECT MAX(t1.Sal)
FROM t1
WHERE t1.Deptno = t.Deptno
AND t1.HireDate = t.HireDate) THEN 1 END Max_Flag
FROM t)
WHERE Min_Flag=1 OR Max_Flag=1;

• Zahar Hilkevich August 11, 2016 / 10:47 am

This may work though it is more complicated than it should be

3. Krishna Jamal August 12, 2016 / 12:55 pm

WITH t AS
(SELECT Ename, Job, Deptno, HireDate, Sal,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY Deptno ORDER BY HireDate) = 1
THEN 1
WHEN MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) = Sal
AND COUNT(Sal) OVER(PARTITION BY Sal ORDER BY HireDate) = 1
THEN 1
END Mn_Flag,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY Deptno ORDER BY HireDate) = 1
THEN 1
WHEN MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) = Sal
AND COUNT(Sal) OVER(PARTITION BY Sal ORDER BY HireDate) = 1
THEN 1
END MX_Flag
FROM Emp)
SELECT * FROM t
WHERE Mn_Flag = 1 OR Mx_Flag = 1;

• Krishna Jamal August 14, 2016 / 8:54 am

WITH t AS
(SELECT Ename, Job, Deptno, HireDate, Sal,
CASE
WHEN MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) = Sal
AND COUNT(Sal) OVER(PARTITION BY Sal ORDER BY HireDate) = 1
THEN 1
END Mn_Flag,
CASE
WHEN MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) = Sal
AND COUNT(Sal) OVER(PARTITION BY Sal ORDER BY HireDate) = 1
THEN 1
END MX_Flag
FROM Emp)
SELECT * FROM t
WHERE Mn_Flag = 1 OR Mx_Flag = 1;

4. Krishna Jamal August 14, 2016 / 8:53 am

WITH tbl AS
(
SELECT Ename, Job, Deptno, Hiredate, Sal,
DECODE(Sal, MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate), 1) Mn_Flag,
DECODE(Sal, MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate), 1) Mx_Flag
FROM Emp
WHERE HireDate IN (SELECT MIN(Hiredate) FROM Emp GROUP BY Sal)
)
SELECT * FROM tbl
WHERE Mn_Flag=1 OR Mx_Flag=1;

5. Krishna Jamal August 15, 2016 / 6:55 am

WITH x AS
(
SELECT Ename, Job, Deptno, HireDate, Sal,
MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mn,
MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mx
FROM Emp
WHERE HireDate IN (SELECT MIN(HireDate) FROM Emp GROUP BY Sal)
)
SELECT Ename, Job, Deptno, HireDate, Sal, DECODE(Sal, Mn, 1) Min_Flag, DECODE(Sal, Mx, 1) Max_Flag
FROM x
WHERE Sal IN (Mn, Mx);

6. Krishna Jamal August 15, 2016 / 7:14 am

WITH x AS
(
SELECT Ename, Job, Deptno, HireDate, Sal,
MIN(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mn,
MAX(Sal) OVER(PARTITION BY Deptno ORDER BY HireDate) Mx
FROM Emp
WHERE HireDate IN (SELECT MIN(HireDate) FROM Emp GROUP BY Sal)
)
SELECT Ename, Job, Deptno, HireDate, Sal,
NVL2(NULLIF(Sal,Mn), NULL, 1) Min_Flag,
NVL2(NULLIF(Sal,Mx), NULL, 1) Max_Flag
FROM x
WHERE Sal IN (Mn, Mx);