# Top Salary Puzzle

*Find highest salary in each department without using MAX function*

- Use a single SELECT statement only.
- For an added complexity (optional): try not using ANY functions at all (neither group, nor analytic, not even scalar)

**Expected Result**:

DEPTNO | MAX_SAL |
---|---|

10 | 5000 |

20 | 3000 |

30 | 2850 |

# Solutions:

We will begin with a simpler problem that does allow us using functions.

### Solution #1. Using MIN function

Credit to: **Boobal Ganesan**

MIN function can be seen as an opposite to the MAX, so it is trivial to employ it here:

SELECT deptno, -MIN(-sal) max_sal FROM scott.emp GROUP BY deptno ORDER BY 1;

### Solution #2. Using LISTAGG and REGEXP_SUBSTR functions

This is an “order” based approach that sorts the values within a concatenated string and then uses regular expression to cut the first token.

SELECT deptno, REGEXP_SUBSTR(LISTAGG(sal,',') WITHIN GROUP(ORDER BY sal DESC),'[^,]+',1,1) max_sal FROM scott.emp GROUP BY deptno ORDER BY 1;

### Solution #3. Using AVG(…) KEEP() group function

This is another “order” based strategy whete AVG function can be replaced with MIN or any other aggregate function that returns a single value out of a set of identical ones.

SELECT deptno, AVG(sal) KEEP(DENSE_RANK FIRST ORDER BY sal DESC) max_sal FROM scott.emp GROUP BY deptno ORDER BY 1;

### Solution #4. Using Analytic function and CTE

ROW_NUMBER is chosen in this approach, though other analytic functions, such as RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, etc can be used here (with some changes) as well. ROW_NUMBER is convenient to use as it allows to avoid DISTINCT option.

WITH x AS ( SELECT deptno, sal, ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY sal DESC) rn FROM scott.emp ) SELECT deptno, sal max_sal FROM x WHERE rn=1 ORDER BY 1;

### Solution #5. Using MATCH_RECOGNIZE clause

Credit to: **KATAYAMA NAOTO**

This approach is similar to the previous one if we used LAG analytic function: which would return NULL for the top record.

SELECT deptno, sal max_sal FROM scott.emp MATCH_RECOGNIZE ( PARTITION BY deptno ORDER BY sal DESC ALL ROWS PER MATCH PATTERN (ISNULL) DEFINE ISNULL AS PREV(ISNULL.sal) IS NULL );

### Solution #6. CONNECT BY and CONNECT_BY_ISLEAF while avoiding Analytic functions

This approach is a bit artificial. We could have used DISTINCT and avoid START WITH clause completely. CTEs **x** and **y **are used to simulate ROW_NUMBER analytic function.

WITH x AS ( SELECT deptno, sal FROM scott.emp ORDER BY 1,2 ), y AS ( SELECT x.*, ROWNUM rn FROM x ) SELECT deptno, sal FROM y WHERE CONNECT_BY_ISLEAF=1 CONNECT BY deptno=PRIOR deptno AND rn=PRIOR rn+1 START WITH (deptno, rn) IN (SELECT deptno, MIN(rn) FROM y GROUP BY deptno);

### Solution #7. Using MODEL clause with ROW_NUMBER function

This method is pretty much the same as in the Solution #4 above. The RETURN UPDATED ROWS and dummy measures are used to only return rows with rn=1.

SELECT deptno, max_sal FROM scott.emp MODEL RETURN UPDATED ROWS PARTITION BY (deptno) DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) rn) MEASURES(sal max_sal, 0 dummy) RULES( dummy[1]=1 ) ORDER BY 1;

**The following 5 solutions (##8-12) satisfy the “added complexity” term and do NOT use any functions at all.**

### Solution #8. Using ALL predicate

Generally speaking, >=ALL filter is identical to =(SELECT MAX() …). See my book for more detailed explanations.

SELECT deptno, sal max_sal FROM scott.emp a WHERE sal>=ALL(SELECT sal FROM scott.emp WHERE deptno=a.deptno) GROUP BY deptno, sal ORDER BY 1;

### Solution #9. Using NOT EXISTS predicate

See Chapter 10 of my book for details.

SELECT deptno, sal max_sal FROM scott.emp a WHERE NOT EXISTS(SELECT 1 FROM scott.emp WHERE deptno=a.deptno AND sal>a.sal) GROUP BY deptno, sal ORDER BY 1;

### Solution #10. Using Outer-Join with IS NULL filter

This approach is also covered very deeply in my book, Chapter 10.

SELECT a.deptno, a.sal max_sal FROM scott.emp a LEFT JOIN scott.emp b ON a.deptno=b.deptno AND b.sal>a.sal WHERE b.empno IS NULL GROUP BY a.deptno, a.sal ORDER BY 1;

### Solution #11. Using MINUS and ANY predicate

MINUS serves 2 purposes: it removes non-top rows and eliminates duplicates, so no DISTINCT option (or GROUP BY) is required.

SELECT deptno, sal max_sal FROM scott.emp MINUS SELECT deptno, sal FROM scott.emp a WHERE sal<ANY(SELECT sal FROM scott.emp WHERE deptno=a.deptno);

### Solution #12. Using MINUS and EXISTS predicate

Last two approaches covered in the drill from the Chapter 10 of my book.

SELECT deptno, sal max_sal FROM scott.emp MINUS SELECT deptno, sal FROM scott.emp a WHERE EXISTS(SELECT 1 FROM scott.emp WHERE deptno=a.deptno AND sal>a.sal);

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.