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 |
We will begin with a simpler problem that does allow us using functions.
Solution #1. Using MIN function
Credit to:
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:
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.
sqlpatterns 