# Top and Bottom Paid Employees

List top and bottom paid employees in each department without using UNION [ALL] operator

• Use a single SELECT statement only.
• SET Operators are not allowed
• You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points.
• The scoring rules can be found here.
• Solutions must be submitted as comments to this blog post.
• Use <pre>or <code> html tags around your SQL code for better formatting and to avoid losing parts of your SQL.

Expected Result:

ENAME DEPTNO SAL
KING 10 5000
MILLER 10 1300
SCOTT 20 3000
FORD 20 3000
SMITH 20 800
BLAKE 30 2850
JAMES 30 950

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

If you like this post, you may want to join my 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.

### 11 thoughts on “2018 Oracle SQL Puzzle of the Week #12”

1. KATAYAMA NAOTO March 26, 2018 / 5:19 pm

SELECT ENAME,DEPTNO,SAL
FROM (
SELECT EMP.*
,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) T
,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) B
FROM EMP)
WHERE T=1 OR B=1
ORDER BY DEPTNO, T DESC;

• KATAYAMA NAOTO March 26, 2018 / 5:32 pm

what is SET Operators ?

• Zahar Hilkevich March 27, 2018 / 9:52 am

UNION [ALL], MINUS and INTERSECT are called SET operators

2. Boobal Ganesan March 26, 2018 / 10:53 pm

```WITH t1 AS ( SELECT deptno, sal FROM ( SELECT deptno, MAX(sal) max_sal, MIN(sal) min_sal FROM emp GROUP BY deptno ) UNPIVOT ( sal FOR i IN ( "MAX_SAL", "MIN_SAL" ) ) ) SELECT emp.ename, emp.deptno, emp.sal FROM emp, t1 WHERE emp.deptno = t1.deptno AND emp.sal = t1.sal ORDER BY deptno, sal DESC;```

3. Boobal Ganesan March 26, 2018 / 11:00 pm

WITH t1 AS (
SELECT DISTINCT
deptno,
sal
FROM
emp
),t2 AS (
SELECT
deptno,
LISTAGG(sal,
‘,’) WITHIN GROUP(
ORDER BY
sal DESC
) sal
FROM
emp
GROUP BY
deptno
),t3 AS (
SELECT
deptno,
regexp_substr(sal,'[^,]+’,1,1) max_sal,
regexp_substr(sal,'[^,]+’,1,regexp_count(sal,’,’) + 1) min_sal
FROM
t2
),t4 AS (
SELECT
*
FROM
t3 UNPIVOT ( sal
FOR i
IN ( “MAX_SAL”,
“MIN_SAL” ) )
) SELECT
emp.ename,
emp.deptno,
emp.sal
FROM
t4,
emp
WHERE
t4.deptno = emp.deptno
AND t4.sal = emp.sal
ORDER BY
emp.deptno,
emp.sal DESC;

4. Boobal Ganesan March 26, 2018 / 11:14 pm

```WITH t1 AS ( SELECT deptno, MAX(sal) max_sal FROM emp GROUP BY deptno ),t2 AS ( SELECT deptno, MIN(sal) min_sal FROM emp GROUP BY deptno ),t3 AS ( SELECT t1.deptno, t1.max_sal, t2.min_sal FROM t1, t2 WHERE t1.deptno = t2.deptno ), t4 as (SELECT * FROM t3 unpivot ( sal for i in ("MAX_SAL","MIN_SAL"))) select emp.ename,emp.deptno,emp.sal from t4,emp where t4.deptno=emp.deptno and t4.sal=emp.sal order by deptno,sal desc;```

5. Boobal Ganesan March 26, 2018 / 11:27 pm

```SELECT t1.ename, t1.deptno, t1.sal FROM emp t1 WHERE t1.sal IN ( SELECT MAX(t2.sal) FROM emp t2 WHERE t1.deptno = t2.deptno ) OR t1.sal IN ( SELECT MIN(t2.sal) FROM emp t2 WHERE t1.deptno = t2.deptno ) order by deptno,sal desc,ename```

6. Mahantesh Hiremath March 26, 2018 / 11:37 pm

SELECT ENAME,DEPTNO,SAL FROM
(
SELECT ENAME,DEPTNO,SAL,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) T FROM
(
SELECT EMP.ENAME,EMP.DEPTNO,EMP.SAL

FROM scott.emp EMP ) A

WHERE
(DEPTNO,SAL) IN (
SELECT DEPTNO,MAX(SAL)
FROM scott.emp
GROUP BY DEPTNO)
OR
(DEPTNO,SAL) IN (
SELECT DEPTNO,MIN(SAL)
FROM scott.emp
GROUP BY DEPTNO)
) mm
ORDER BY mm.DEPTNO, mm.T

7. Boobal Ganesan March 26, 2018 / 11:40 pm

WITH t1 AS (
SELECT
t1.ename,
t1.deptno,
t1.sal,
CASE
WHEN t1.sal IN (
SELECT
MAX(t2.sal)
FROM
emp t2
WHERE
t1.deptno = t2.deptno
) THEN 1
WHEN t1.sal IN (
SELECT
MIN(t2.sal)
FROM
emp t2
WHERE
t1.deptno = t2.deptno
) THEN 2
END
sal_rnk
FROM
emp t1
) SELECT
ename,
deptno,
sal
FROM
t1
WHERE
sal_rnk IN (
1,
2
)
ORDER BY
deptno,
sal DESC,
ename;

8. Boobal Ganesan March 26, 2018 / 11:50 pm

```WITH t1 AS ( SELECT deptno, MAX(sal) max_sal, MIN(sal) min_sal FROM emp GROUP BY deptno ) SELECT emp.ename, emp.deptno, emp.sal FROM t1, emp WHERE t1.deptno = emp.deptno AND emp.sal IN ( t1.max_sal, t1.min_sal ) ORDER BY emp.deptno, emp.sal DESC, emp.ename;```

9. KATAYAMA NAOTO March 30, 2018 / 7:33 pm

SELECT ENAME, DEPTNO, SAL FROM EMP
MODEL RETURN UPDATED ROWS
PARTITION BY(DEPTNO)
DIMENSION BY(
EMPNO
,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL) T
,DENSE_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) B)
MEASURES(ENAME,SAL,0 DUMMY)
RULES(DUMMY[ANY,1,ANY]ORDER BY EMPNO = 1,DUMMY[ANY,ANY,1]ORDER BY EMPNO = 1)
ORDER BY DEPTNO,SAL DESC,EMPNO;