In the previous blog post on that subject we reviewed a couple a notable applications of Oracle analytic functions. Today, I came across another interesting illustration of the same concept.
Problem: List all employees from the same department and holding the same job title as ADAMS.
Expected Result:
| ENAME | JOB | DEPTNO |
|---|---|---|
| ADAMS | CLERK | 20 |
| SMITH | CLERK | 20 |
Like before, we start with traditional approaches that every experienced developer would easily demonstrate.
Strategy #1: Using multi-column subquery
SELECT ename, job, deptno
FROM scott.emp
WHERE (deptno, job) IN (SELECT deptno, job
FROM scott.emp
WHERE ename = 'ADAMS')
ORDER BY 2, 3, 1
Strategy #2: Using self-join
SELECT a.ename, job, deptno FROM scott.emp a JOIN scott.emp b USING(deptno, job) WHERE b.ename = 'ADAMS' ORDER BY job, deptno, a.ename
Strategy #3: Using EXISTS predicate
SELECT ename, job, deptno
FROM scott.emp a
WHERE EXISTS (SELECT 1
FROM scott.emp
WHERE ename = 'ADAMS'
AND deptno = a.deptno
AND job = a.job)
ORDER BY 2, 3, 1
A common feature of all the strategies above is having two copies of the emp table with two joining conditions (deptno, job) and one filter (ename = ‘ADAMS’)
As we have seen before, with analytic functions, we can get away with a single copy of th emp table.
Strategy #4: Using COUNT Analytic function
WITH x AS (
SELECT ename, job, deptno,
COUNT(DECODE(ename, 'ADAMS', 1))
OVER(PARTITION BY deptno, job) cnt
FROM scott.emp
)
SELECT ename, job, deptno
FROM x
WHERE cnt > 0
ORDER BY 2, 3, 1
Of course, you can use different analytic functions here:
Strategy #5: Using MAX Analytic function
WITH x AS (
SELECT ename, job, deptno,
MAX(DECODE(ename, 'ADAMS', ename))
OVER(PARTITION BY deptno, job) adams
FROM scott.emp
)
SELECT ename, job, deptno
FROM x
WHERE adams = 'ADAMS'
ORDER BY 2, 3, 1
Strategy #6: Using LISTAGG Analytic function
WITH x AS (
SELECT ename, job, deptno,
LISTAGG(DECODE(ename, 'ADAMS', 'Y'), '|') WITHIN GROUP (ORDER BY 1)
OVER(PARTITION BY deptno, job) flag
FROM scott.emp
)
SELECT ename, job, deptno
FROM x
WHERE flag LIKE 'Y%'
ORDER BY 2, 3, 1
We need to use LIKE operator in case we have more than a single Adams working in the same department and holding the same job title.
***
If you find this post useful, please press the LIKE button and subscribe.
My Oracle Group on Facebook:
Also, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Suggested Reading:
Would you like to read about many more tricks and puzzles? For more clever tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.
sqlpatterns 