## 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.

### Expected Result:

**#1. Using Common Table Expression (CTE) or Recursive WITH clause**

WITH y AS ( SELECT ename, job, deptno, hiredate, sal, ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY hiredate) rn FROM emp ), x (ename, job, deptno, hiredate, sal, min_sal, max_sal, min_flag, max_flag, rn) AS ( SELECT ename, job, deptno, hiredate, sal, sal, sal, 1, 1, 1 FROM y WHERE rn=1 UNION ALL SELECT y.ename, y.job, y.deptno, y.hiredate, y.sal, LEAST(x.min_sal, y.sal), GREATEST(x.max_sal, y.sal), CASE WHEN y.sal<x.min_sal THEN 1 END, CASE WHEN y.sal>x.max_sal THEN 1 END, y.rn FROM y JOIN x ON y.deptno=x.deptno AND y.rn=x.rn+1 ) SELECT ename, job, deptno, hiredate, sal, min_flag, max_flag FROM x WHERE 1 IN (min_flag, max_flag) ORDER BY deptno, hiredate;

**#2. Using Cumulative Analytic Functions MIN, MAX, and ROW_NUMBER**

WITH x AS ( SELECT ename, job, deptno, hiredate, sal, MIN(sal)OVER(PARTITION BY deptno ORDER BY hiredate) min_sal, MAX(sal)OVER(PARTITION BY deptno ORDER BY hiredate) max_sal, ROW_NUMBER()OVER(PARTITION BY deptno, sal ORDER BY hiredate) rn FROM emp ) SELECT ename, job, deptno, hiredate, sal, DECODE(sal, min_sal, 1) min_flag, DECODE(sal, max_sal, 1) max_flag FROM x WHERE sal IN (min_sal, max_sal) AND rn=1;

#3. **Using Cumulative Analytic Functions MIN, MAX, and COUNT**

WITH x AS ( SELECT ename, job, deptno, hiredate, sal, CASE WHEN MIN(sal)OVER(PARTITION BY deptno ORDER BY hiredate)=sal AND COUNT(*)OVER(PARTITION BY deptno, sal ORDER BY hiredate)=1 THEN 1 END min_flag, CASE WHEN MAX(sal)OVER(PARTITION BY deptno ORDER BY hiredate)=sal AND COUNT(*)OVER(PARTITION BY deptno, sal ORDER BY hiredate)=1 THEN 1 END max_flag FROM emp ) SELECT * FROM x WHERE 1 IN (min_flag, max_flag);

**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.