6 Solutions to 2018 Oracle SQL Puzzle of the Week #12

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

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

Solutions:

Solution #1. Using two RANK functions and LEAST

WITH x AS (
SELECT ename, deptno, sal, 
       RANK()OVER(PARTITION BY deptno ORDER BY sal DESC) rkd,
       RANK()OVER(PARTITION BY deptno ORDER BY sal ASC) rka
FROM scott.emp
)
SELECT ename, deptno, sal
FROM x
WHERE LEAST(rkd, rka)=1
ORDER BY deptno, sal DESC

Solution #2. Using RANK and LEAST with MODEL clause

SELECT ename, deptno, sal 
FROM scott.emp 
MODEL 
RETURN UPDATED ROWS 
PARTITION BY (deptno) 
DIMENSION BY (LEAST(RANK()OVER(PARTITION BY deptno ORDER BY sal DESC), 
                    RANK()OVER(PARTITION BY deptno ORDER BY sal ASC)) rk, 
               ROWID rid) 
MEASURES(ename, sal, 0 AS dummy) 
RULES( dummy[1, ANY]=1 ) 
ORDER BY deptno, sal DESC;

Solution #3. Using ALL predicates with OR

SELECT ename, deptno, sal
FROM scott.emp a
WHERE sal>=ALL(SELECT sal FROM scott.emp WHERE deptno=a.deptno)
   OR sal<=ALL(SELECT sal FROM scott.emp WHERE deptno=a.deptno)
ORDER BY deptno, sal DESC

Solution #4. Using MIN/MAX, CTE and UNPIVOT clause

WITH x AS ( 
SELECT deptno, MIN(sal) min_sal, MAX(sal) max_sal 
FROM scott.emp 
GROUP BY deptno 
), y AS ( 
SELECT * 
FROM x 
UNPIVOT (sal FOR typ IN (min_sal, max_sal) ) 
) 
SELECT e.ename, e.deptno, e.sal 
FROM y JOIN scott.emp e ON y.deptno=e.deptno AND y.sal=e.sal 
ORDER BY deptno, sal DESC;

Solution #5. Using CTE with MIN/MAX and Inner Join with IN

WITH x AS ( 
SELECT deptno, MIN(sal) min_sal, MAX(sal) max_sal 
FROM scott.emp GROUP BY deptno 
) 
SELECT e.ename, e.deptno, e.sal 
FROM x JOIN scott.emp e ON x.deptno=e.deptno 
                       AND e.sal IN (x.max_sal, x.min_sal) 
ORDER BY 2, 3 DESC;

Solution #6. Using multi-column correlated subquery

SELECT ename, deptno, sal 
FROM scott.emp a 
WHERE (deptno, 1) IN (SELECT deptno, DECODE(a.sal, MIN(sal), 1, 
                                                   MAX(sal), 1) 
 FROM scott.emp 
 GROUP BY deptno) 
ORDER BY 2, 3 DESC;

 

You can execute the above SQL statements in Oracle Live SQL environment.
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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s