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