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