## A trick that helps avoiding multiple table scans.

Let’s look at a fairly simple SQL problem:

In a traditional scott.emp table, find all employees who work in the same department as the president.

• Make your query work even if there are more than 1 president records exist in emp table
• Make Oracle scan emp table just ONCE

A “traditional” solution to this problem may look like this:

Strategy #1: Using a subquery

```SELECT *
FROM scott.emp
WHERE deptno IN (SELECT deptno
FROM scott.emp
WHERE job='PRESIDENT')```

or this:

Strategy #2: Using a self-join

```SELECT DISTINCT a.*
FROM scott.emp a JOIN scott.emp b ON a.deptno=b.deptno
WHERE b.job='PRESIDENT'```

Note, that DISTINCT option in the above query is needed to prevent duplicates if there were multiple presidents in а specific department.

Both solutions above use 2 copies of the emp table which makes oracle scan the same scott.emp table twice.

A trick presented below allows you to use only a single copy of the emp table to solve the problem. The trick involves different conceptual and technical approaches compared to the solutions we have seen so far.

Conceptually, we should rephrase the problem in a way that would keep it identical and at the same time allows us to use different technical arsenal. This approach is explained in a detailed manner in my book “Oracle SQL Tricks and Workarounds”. We can rephrase the puzzle and say that we are looking for employees from departments with some “positive” number of presidents working there. Technically speaking, we need to use analytic function COUNT and check if it is greater than 0:

Strategy #3: Using analytic function COUNT

```WITH x AS (
SELECT e.*,
COUNT(DECODE(job,'PRESIDENT',1))OVER(PARTITION BY deptno) cnt
FROM scott.emp e
)
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM x
WHERE cnt>0
ORDER BY empno```

We do need to use a common table expression as we cannot filter out by analytic function in the same query where the function is used. Nevertheless, we scan the emp table just once, and during this scan, Oracle engine counts the number of presidents in each department.

COUNT is not the only analytic function that can be employed to solve the problem.

Strategy #4: Using analytic function LISTAGG

```WITH x AS (
SELECT e.*,
LISTAGG(job,'|')
WITHIN GROUP (ORDER BY job) OVER(PARTITION BY deptno) jobs
FROM scott.emp e
)
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM x
WHERE '|' || jobs || '|' LIKE '%|PRESIDENT|%'
ORDER BY empno```

Instead of counting the presidents by department, we simply concatenate all the job titles and check if the resulting string includes a president.

Finally, if you don’t like using sub-queries in general, we can leverage the power of MODEL clause:

Strategy #5: Using MODEL clause to avoid sub-queries

```SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM scott.emp
MODEL RETURN UPDATED ROWS
DIMENSION BY (
empno,
SIGN(COUNT(DECODE(job,'PRESIDENT',1))OVER(PARTITION BY deptno)) cnt
)
MEASURES(ename, job, mgr, hiredate, sal, comm, deptno, 0 dummy)
RULES(dummy[ANY, 1]=1)
ORDER BY empno```

The tricky part here is using a composition of SIGN, COUNT, and DECODE functions (i.e. SIGN on top of what we used in Strategy #3) as a secondary dimension and empno as primary. Employee number is unique by itself, so adding another dimension will still maintain uniqueness required by MODEL clause. The only MODEL RULE changes the dummy measure which “triggers” the “RETURN UPDATED ROWS” instruction and returns only those rows where the dummy dimension was set to 1 – notice that its default value is 0.

You can check the execution plan for all of the above strategies to see how many times Oracle scans the emp table.

***

If you find this post useful, please press the LIKE button and subscribe.

Also, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

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

# Yet Another Top Employee Puzzle

Find the employee who remained the top paid employee (excluding the president) the longest period of time between 1980 and 1981

• Use a single SELECT statement only.
• President should be excluded from the analysis.
• Show the number of days the employee remained the top paid person as well as Start Date (hiredate) and End Date (the date when another top employee started)
• The End Date for the last top paid employee in the interval should be 31-DEC-1981.

Expected Result:

EMPNO ENAME JOB SAL Start Date End Date Days on Top
7566 JONES MANAGER 2975 02-APR-81 03-DEC-81 245

# Solutions:

### Solution #1. Using RANK to filter the top employee:

``````WITH x AS (
SELECT empno, ename, job, sal, hiredate,
MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp
WHERE job!='PRESIDENT'
), y AS (
SELECT empno, ename, job, sal, hiredate start_date, max_sal,
FROM x
WHERE sal=max_sal
), z AS (
SELECT y.*, LEAST(end_date, date'1981-12-31')-start_date days_on_top,
RANK()OVER(ORDER BY LEAST(end_date, date'1981-12-31')-start_date DESC) rk
FROM y
WHERE EXTRACT(YEAR FROM start_date) IN (1980, 1981)
)
SELECT empno,ename,job,sal, start_date "Start Date",
end_date "End Date", days_on_top	"Days on Top"
FROM z
WHERE rk=1``````

### Solution #2. Using Subquery to filter the top employee:

``````WITH x AS (
SELECT empno, ename, job, sal, hiredate,
MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp
WHERE job!='PRESIDENT'
AND hiredate>=date'1980-01-01'
), y AS (
SELECT empno, ename, job, sal, hiredate start_date,
LEAST(date'1981-12-31',
FROM x
WHERE sal=max_sal
)
SELECT empno,ename,job,sal, start_date "Start Date",
end_date "End Date", end_date-start_date "Days on Top"
FROM y
WHERE end_date-start_date=(SELECT MAX(end_date-start_date) FROM y)``````

### Solution #3. Using MODEL with RETURN UPDATED ROWS to filter the top employee:

``````WITH e AS (
SELECT empno, ename, sal, job, LEAST(hiredate, date'1981-12-31') hiredate,
MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp
WHERE hiredate>=date'1980-01-01'
AND job!='PRESIDENT'
), x AS (
SELECT empno, ename, job, sal, hiredate,
date'1981-12-31')-hiredate diff,
date'1981-12-31') end_date
FROM e
WHERE sal=max_sal
)
SELECT empno, ename, job, sal, hiredate "Start Date",
end_date "End Date", diff "Days on Top"
FROM x
MODEL RETURN UPDATED ROWS
DIMENSION BY (empno, RANK()OVER(ORDER BY diff DESC) rk)
MEASURES(ename,job,sal, hiredate, end_date, diff, 0 dummy)
RULES(dummy[ANY, 1]=1)``````

The following query will only work as long as there is only 1 top paid employee who stayed on top the longest. In case if we had more than 1 it would only list one of those:

```WITH x AS (
SELECT empno, ename, job, sal, hiredate,
MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp
WHERE job!='PRESIDENT'
), y AS (
SELECT empno, ename, job, sal, hiredate start_date,
LEAST(date'1981-12-31',
LEAST(date'1981-12-31',
FROM x
WHERE sal=max_sal
ORDER BY days_top DESC NULLS LAST, hiredate
)
SELECT *
FROM y
WHERE ROWNUM=1```

You can execute the above SQL statements in Oracle Live SQL environment.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

# Second Top Employee as of the Start of Employment

List all employees who were 2nd top paid in the entire company as of the time their employment started

• Use a single SELECT statement only.
• At the time of employment start the rank of the employee by salary should be 2.
• Show the top salary at the time when the employee started with the company.
• We assume that no employees have ever been terminated since day 1.

Expected Result:

ENAME JOB SAL HIREDATE MAX_SAL
WARD SALESMAN 1250 22-FEB-81 1600
BLAKE MANAGER 2850 01-MAY-81 2975
FORD ANALYST 3000 03-DEC-81 5000
SCOTT ANALYST 3000 19-APR-87 5000

# Solutions:

### Solution #1. Using LATERAL view, RANK and cumulative MAX analytic functions (Oracle 12g+):

``````SELECT e.ename, e.job, e.hiredate, e.sal, r.max_sal
FROM scott.emp e, LATERAL(SELECT a.empno,
RANK() OVER(ORDER BY a.sal DESC) rk,
MAX(a.sal) OVER() max_sal
FROM scott.emp a
WHERE a.hiredate<=e.hiredate) r
WHERE e.empno=r.empno
AND rk=2
ORDER BY e.hiredate``````

### Solution #2. Using CTE, cumulative MAX analytic function and a correlated subquery with COUNT to mimic the filter by RANK:

``````WITH x AS (
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal
FROM scott.emp a
)
SELECT *
FROM x
WHERE 1=(SELECT COUNT(*)
FROM scott.emp
WHERE hiredate<=x.hiredate
AND sal>x.sal)
ORDER BY hiredate``````

### Solution #3. Using CTE, cumulative MAX analytic function and an in-line scalar subquery in SELECT to mimic the RANK:

``````WITH x AS (
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal,
(SELECT COUNT(*)+1
FROM scott.emp
WHERE sal>e.sal
AND hiredate<=e.hiredate) rk
FROM scott.emp e
)
SELECT ename, job, hiredate, sal, max_sal
FROM x
WHERE rk=2
ORDER BY hiredate``````

### Solution #4. Using self-join and Cartesian Product with aggregation:

``````SELECT a.ename, a.job, a.hiredate, a.sal, MAX(b.sal) max_sal
FROM scott.emp a JOIN scott.emp b ON b.hiredate<=a.hiredate
AND b.sal>a.sal
GROUP BY a.ename, a.job, a.hiredate, a.sal
HAVING COUNT(DISTINCT b.empno)=1
ORDER BY a.hiredate``````

### Solution #5. Using CTE and cumulative MAX analytic function (twice):

``````WITH x AS (
SELECT ename, job, hiredate, sal,
MAX(sal) OVER(ORDER BY hiredate) max_sal
FROM scott.emp
), y  AS (
SELECT ename, job, hiredate, sal, max_sal, MAX(sal) OVER(ORDER BY hiredate) max_sal2
FROM x
WHERE sal<max_sal
)
SELECT ename, job, hiredate, sal, max_sal
FROM y
WHERE sal=max_sal2
ORDER BY hiredate``````

### Solution #6. Using regular and recursive CTEs, ROWNUM, GREATEST, and CASE functions (no Analytic functions!):

``````WITH e AS (
SELECT ename, job, sal, hiredate
FROM scott.emp
ORDER BY hiredate
), x AS (
SELECT ename, job, sal, hiredate, ROWNUM rn
FROM e
), y(max_sal, sal2, rn) AS (
SELECT sal, 0, 1
FROM x
WHERE rn=1
UNION ALL
SELECT GREATEST(x.sal, y.max_sal) AS max_sal,
CASE WHEN x.sal>y.max_sal THEN y.max_sal
WHEN x.sal>y.sal2 AND x.sal<=y.max_sal THEN x.sal
ELSE y.sal2
END AS sal2,
x.rn
FROM x JOIN y ON x.rn=y.rn+1
)
SELECT x.ename, x.job, x.sal, x.hiredate, y.max_sal
FROM y JOIN x ON y.rn=x.rn AND y.sal2=x.sal``````

### Solution #7. Using CTE and MODEL clause to mimic Solution #6:

``````WITH x AS (
SELECT *
FROM scott.emp
MODEL
DIMENSION BY (ROW_NUMBER() OVER(ORDER BY hiredate) rn)
MEASURES(ename, job, sal, hiredate, sal max_sal, 0 sal2)
RULES(
max_sal[rn>1] = GREATEST(max_sal[CV()-1], sal[CV()]),
sal2[rn>1] = CASE WHEN sal[CV()]> max_sal[CV()-1] THEN max_sal[CV()-1]
WHEN sal[CV()]> sal2[CV()-1]
AND sal[CV()]<=max_sal[CV()-1] THEN sal[CV()]
ELSE sal2[CV()-1]
END
)
)
SELECT ename, job, sal, hiredate, max_sal
FROM x
WHERE sal=sal2``````

You can execute the above SQL statements in Oracle Live SQL environment.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

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

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

# Recent employment Puzzle

For each location, show 2 most recently hired employees

• Use a single SELECT statement only.
• ename1 and hiredate1 columns should correspond the latest hired employee while ename1 and hiredate1 columns – the previous one

Expected Result:

LOC ENAME1 HIREDATE1 ENAME2 HIREDATE2
NEW YORK MILLER 23-JAN-82 KING 17-NOV-81
CHICAGO JAMES 03-DEC-81 MARTIN 28-SEP-81

# Solutions:

### Solution #1. Using Self-Join and MAX functions

```SELECT d.loc,
MAX(e1.ename) KEEP(DENSE_RANK FIRST ORDER BY e1.hiredate DESC) ename1,
MAX(e1.hiredate) hiredate1,
MAX(e2.ename) KEEP(DENSE_RANK FIRST ORDER BY e2.hiredate DESC) ename2,
MAX(e2.hiredate) hiredate2
FROM scott.emp e1 JOIN scott.emp e2 ON e1.deptno=e2.deptno
AND e1.hiredate>=e2.hiredate
AND e1.ROWID!=e2.ROWID
JOIN scott.dept d ON e1.deptno=d.deptno
GROUP BY d.loc;```

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

```SELECT d.loc,
MAX(ename) KEEP(DENSE_RANK FIRST ORDER BY hiredate DESC) ename1,
MAX(hiredate) hiredate1,
REGEXP_SUBSTR(LISTAGG(ename, '|') WITHIN GROUP (ORDER BY hiredate DESC),
'[^|]+',1,2) ename2,
REGEXP_SUBSTR(LISTAGG(hiredate, '|') WITHIN GROUP (ORDER BY hiredate DESC),
'[^|]+',1,2) hiredate2
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
GROUP BY d.loc
ORDER BY 1;```

### Solution #3. Using CTE, ROW_NUMBER, and Self-Join

```WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT a.loc, a.ename ename1, a.hiredate hiredate1,
b.ename ename2, b.hiredate hiredate2
FROM x a JOIN x b ON a.loc=b.loc AND a.rn=1 AND b.rn=2;```

### Solution #4. Using Pivot

```WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc, e1_ename AS ename1, e1_hdate AS hiredate1,
e2_ename AS ename2, e2_hdate AS hiredate2
FROM x
PIVOT (
MAX(ename) ename, MAX(hiredate) hdate FOR rn IN (1 AS e1, 2 AS e2)
)
ORDER BY 1;```

### Solution #5. Simulating Pivot with MAX and DECODE functions

```WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc,
MAX(DECODE(rn,1,ename)) ename1,
MAX(DECODE(rn,1,hiredate)) hiredate1,
MAX(DECODE(rn,2,ename)) ename2,
MAX(DECODE(rn,2,hiredate)) hiredate2
FROM x
GROUP BY loc
ORDER BY 1;```

### Solution #6. Using CONNECT BY

```WITH x AS (
SELECT d.loc, e.ename, e.hiredate,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc, PRIOR ename ename1, PRIOR hiredate hiredate1,
ename ename2, hiredate hriedate2
FROM x
WHERE rn=2
CONNECT BY loc=PRIOR loc
AND rn=PRIOR rn+1;```

### Solution #7. Using LEAD and ROW_NUMBER Analytic functions

```WITH x AS (
SELECT d.loc, e.ename ename1, e.hiredate hiredate1,
LEAD(e.ename,1) OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) ename2,
LEAD(e.hiredate,1) OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) hiredate2,
ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
)
SELECT loc, ename1, hiredate1, ename2, hiredate2
FROM x
WHERE rn=1
ORDER BY 1;```

### Solution #8. Using Model Clause:

```SELECT loc, ename1, hiredate1, ename2, hiredate2
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno
MODEL
RETURN UPDATED ROWS
PARTITION BY (d.loc)
DIMENSION BY (
ROW_NUMBER()OVER(PARTITION BY d.loc ORDER BY e.hiredate DESC) AS rn
)
MEASURES(
ename AS ename1, hiredate AS hiredate1,
ename AS ename2, hiredate AS hiredate2
)
RULES(
ename2[1]   =ename1[2],
hiredate2[1]=hiredate1[2]
)
ORDER BY 1;
```

You can execute the above SQL statements in Oracle Live SQL environment.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

# Triangle Numbers Puzzle

Generate a sequence of first N triangle numbers: 1, 3 (=1+2); 6=(1+2+3), 10=(1+2+3+4), etc

• Use a single SELECT statement only.
• Do not use any mathematical formulas, except for the sequence definition.

Expected Result (for N=10):

N TRAINGLE_N
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55

# Solutions:

### Solution #1: Using Cumulative SUM analytic function:

```SELECT LEVEL n, SUM(LEVEL) OVER(ORDER BY LEVEL) triangle_n
FROM dual
CONNECT BY LEVEL<=10```

### Solution #2: Using MODEL clause with ITERATE:

```SELECT n, tn triangle_n
FROM dual
MODEL
RETURN UPDATED ROWS
DIMENSION BY (0 AS N)
MEASURES(0 AS TN)
RULES ITERATE(10)
(TN[ITERATION_NUMBER+1]=TN[cv()-1]+ITERATION_NUMBER+1)```

### Solution #3: Using MODEL clause over generated range:

```WITH x AS (
SELECT ROWNUM-1 rn
FROM dual
CONNECT BY LEVEL<=11
)
SELECT n, tn triangle_n
FROM x
MODEL
RETURN UPDATED ROWS
DIMENSION BY (rn)
MEASURES(0 AS tn, rn AS n)
RULES(tn[rn>=1]=tn[CV()-1]+n[CV()])```

### Solution #4: Using XMLQUERY and SYS_CONNECT_BY_PATH functions:

Credit to Boobal Ganesan

```SELECT LEVEL n,
XMLQUERY(SYS_CONNECT_BY_PATH(LEVEL,'+')
RETURNING CONTENT).getnumberval() triangle_n
FROM dual
CONNECT BY level <= 10```

### Solution #5: Using Recursive CTE:

```WITH x(n,triangle_n) AS (
SELECT 1,1
FROM dual
UNION ALL
SELECT n+1, triangle_n+n+1
FROM x
WHERE n<10
)
SELECT *
FROM x```

### Solution #6: Using CTE and Self-Join:

```WITH x AS (
SELECT ROWNUM n
FROM dual
CONNECT BY LEVEL<=10
)
SELECT a.n, SUM(b.n) triangle_n
FROM x a JOIN x b ON a.n>=b.n
GROUP BY a.n
ORDER BY 1```

### Solution #7: Using CTE and LATERAL view:

```WITH x AS (
SELECT ROWNUM n
FROM dual
CONNECT BY LEVEL<=10
)
SELECT a.n, t.triangle_n
FROM x a, LATERAL(SELECT SUM(b.n) triangle_n
FROM x b
WHERE b.n<=a.n) t```

You can execute the above SQL statements in Oracle Live SQL environment.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

# Namesake Puzzle

Show groups of employees having the same last name.

• Use a single SELECT statement only
• Use hr.employees table

# Solutions:

### Solution #1: Using Subquery with HAVING clause:

``````SELECT first_name, last_name, department_id, employee_id
FROM hr.employees
WHERE last_name IN (SELECT last_name
FROM hr.employees
GROUP BY last_name
HAVING COUNT(*)>1)
ORDER BY 2,1``````

### Solution #2: Using Multi-Column Subquery with NO HAVING clause

``````SELECT first_name, last_name, department_id, employee_id
FROM hr.employees
WHERE (last_name, 1) IN (SELECT last_name, SIGN(COUNT(*)-1)
FROM hr.employees
GROUP BY last_name)
ORDER BY 2,1``````

### Solution #3: Using Subquery with IN operator

``````SELECT first_name, last_name, department_id, employee_id
FROM hr.employees a
WHERE last_name IN (SELECT b.last_name
FROM hr.employees b
WHERE a.employee_id!=b.employee_id)
ORDER BY 2,1``````

### Solution #4: Using Self-Join with duplicate elimination in GROUP BY

``````SELECT a.first_name, a.last_name, a.department_id, a.employee_id
FROM hr.employees a JOIN hr.employees b ON a.last_name=b.last_name
AND a.employee_id!=b.employee_id
GROUP BY a.first_name, a.last_name, a.department_id, a.employee_id
ORDER BY 2,1``````

### Solution #5: Using a filter by COUNT analytic function with PARTITION BY

``````WITH x AS (
SELECT first_name, last_name, department_id, employee_id,
COUNT(*) OVER(PARTITION BY last_name) cnt
FROM hr.employees
)
SELECT first_name, last_name, department_id, employee_id
FROM x
WHERE cnt>1
ORDER BY 2,1``````

### Solution #6: Mimicking COUNT analytic function with MODEL clause

(credit to Naoto Katayama)

``````WITH x AS (
SELECT first_name, last_name, department_id, employee_id, cnt
FROM hr.employees
MODEL
RETURN UPDATED ROWS
DIMENSION BY (last_name, employee_id)
MEASURES(first_name, department_id, 0 AS cnt)
RULES (cnt[ANY, ANY]=COUNT(*)[CV(), ANY])
)
SELECT first_name, last_name, department_id, employee_id
FROM x
WHERE cnt>1
ORDER BY 2,1``````

### Solution #7: Filtering by LEAD and LAG analytic functions

``````WITH x AS (
SELECT first_name, last_name, department_id, employee_id,
LAG (last_name,1) OVER(ORDER BY last_name) lag_name,
FROM hr.employees
)
SELECT first_name, last_name, department_id, employee_id
FROM x
ORDER BY 2,1``````

### Solution #8: Using MODEL clause with dummy measure for SIGN over analytic function expression

``````SELECT first_name, last_name, department_id, employee_id
FROM hr.employees
MODEL
RETURN UPDATED ROWS
PARTITION BY (last_name)
DIMENSION BY (SIGN(COUNT(*) OVER(PARTITION BY last_name)-1) AS n,
employee_id)
MEASURES(first_name, department_id, 0 AS dummy)
RULES (dummy[1, ANY]=1)
ORDER BY 2,1``````

### Solution #9: Using UNPIVOT with DISTINCT option over CONNECT BY with PRIOR

``````WITH x AS (
SELECT first_name curr_first, last_name, department_id curr_dept, employee_id curr_id,
PRIOR first_name prior_first, PRIOR department_id prior_dept, PRIOR employee_id prior_id
FROM hr.employees
WHERE level=2
CONNECT BY last_name=PRIOR last_name AND employee_id>PRIOR employee_id
)
SELECT DISTINCT first_name, last_name, department_id, employee_id
FROM x
UNPIVOT(
(first_name, department_id, employee_id)  for dummy IN ((curr_first, curr_dept, curr_id),
(prior_first,prior_dept,prior_id))
)
ORDER BY 2,1;``````

You can execute the above SQL statements in Oracle Live SQL environment.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

# Conference Team Puzzle

Research department from Dallas (#20) needs to delegate a team of 3 to a annual conference. Create a list of all possible teams of 3 employees from that department.

• Use a single SELECT statement only
• Use scott.emp table
• Exactly 3 employees (no repetitions) must be presented for each team

# Solutions:

Essentially, all 6 solutions represent 6 different ways how you can UNPIVOT a result set. Some of those are fairly standard and well known (#3, #4, and #7) while the others are quite tricky (#2, #5, and #6).

## Solution #1: Using UNPIVOT clause:

``````WITH t AS (  --#1: Using UNPIVOT
SELECT ename, empno
FROM scott.emp
WHERE deptno=20
), x AS (
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3
FROM t t1 JOIN t t2 ON t1.empno>t2.empno
JOIN t t3 ON t2.empno>t3.empno
)
SELECT team_no, team_member
FROM x
UNPIVOT (team_member FOR dummy IN (e1,e2,e3) )
ORDER BY 1,2``````

## Solution #2: Mimicking UNPIVOT with IN operator

``````WITH t AS (  --#2: Mimicking UNPIVOT with IN operator
SELECT ename, empno
FROM scott.emp
WHERE deptno=20
), x AS (
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3
FROM t t1 JOIN t t2 ON t1.empno>t2.empno
JOIN t t3 ON t2.empno>t3.empno
)
SELECT x.team_no, t.ename team_member
FROM t JOIN x ON t.ename IN (x.e1, x.e2, x.e3)
ORDER BY 1,2``````

## Solution #3: Mimicking UNPIVOT with MODEL clause

``````WITH t AS ( --#3: Mimicking UNPIVOT with MODEL clause
SELECT ename, empno
FROM scott.emp
WHERE deptno=20
), x AS (
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3
FROM t t1 JOIN t t2 ON t1.empno>t2.empno
JOIN t t3 ON t2.empno>t3.empno
)
SELECT team_no, team_member
FROM x
MODEL
PARTITION BY (team_no)
DIMENSION BY (1 AS dummy)
MEASURES (e1 AS team_member, e2, e3)
RULES(
team_member[2]=e2[1],
team_member[3]=e3[1]
)
ORDER BY 1,2``````

## Solution #4: Mimicking UNPIVOT with UNION operators

``````WITH t AS ( --#4: Mimicking UNPIVOT with UNIONs
SELECT ename, empno
FROM scott.emp
WHERE deptno=20
), x AS (
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3
FROM t t1 JOIN t t2 ON t1.empno>t2.empno
JOIN t t3 ON t2.empno>t3.empno
)
SELECT team_no, e1 team_member
FROM x
UNION
SELECT team_no, e2 team_member
FROM x
UNION
SELECT team_no, e3 team_member
FROM x``````

## Solution #5: Mimicking UNPIVOT with DECODE and Cartesian Product

``````WITH t AS ( --#5: Mimicking UNPIVOT with DECODE and Cartesian Product
SELECT ename, empno
FROM scott.emp
WHERE deptno=20
), x AS (
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3
FROM t t1 JOIN t t2 ON t1.empno>t2.empno
JOIN t t3 ON t2.empno>t3.empno
)
SELECT team_no, DECODE(y.rn, 1, e1, 2, e2, 3, e3) team_member
FROM x, (SELECT ROWNUM rn FROM dual CONNECT BY LEVEL<=3) y
ORDER BY 1,2``````

## Solution #6: Mimicking UNPIVOT with COALESCE and GROUPING SETS

``````WITH t AS ( --#6: Mimicking UNPIVOT with COALESCE and GROUPING SETS
SELECT ename, empno
FROM scott.emp
WHERE deptno=20
), x AS (
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3
FROM t t1 JOIN t t2 ON t1.empno>t2.empno
JOIN t t3 ON t2.empno>t3.empno
)
SELECT team_no, COALESCE(e1, e2, e3) team_member
FROM x
GROUP BY team_no, GROUPING SETS(e1,e2,e3)
ORDER BY 1,2``````

## Solution #7: Mimicking UNPIVOT with Recursive CTE

```WITH t AS ( --#7: Mimicking UNPIVOT with Recursive CTE
SELECT ename, empno
FROM scott.emp
WHERE deptno=20
), x AS (
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3
FROM t t1 JOIN t t2 ON t1.empno>t2.empno
JOIN t t3 ON t2.empno>t3.empno
), y(team_no, team_member, e2, e3, lvl) AS (
SELECT team_no, e1, e2, e3, 1
FROM x
UNION ALL
SELECT team_no, DECODE(lvl+1, 2, e2, e3), e2, e3, lvl+1
FROM y
WHERE lvl+1<=3
)
SELECT team_no, team_member
FROM y
ORDER BY 1,2
```

You can execute the above SQL statements in Oracle Live SQL environment.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## SQL Puzzle:

Generate a term replication sequence: 1, 2, 2, 3, 3, 3, 4, 4, 4, 4, etc in a single SELECT statement.

Expected Result (for N=4):

RN
1
2
2
3
3
3
4
4
4
4

## Solutions:

#1: Using CONNECT BY (for both, the range and the sequence generation)

```WITH x AS (
SELECT ROWNUM rn
FROM dual
CONNECT BY LEVEL<=4
)
SELECT rn--, LEVEL
FROM x
CONNECT BY LEVEL<=rn
AND rn>PRIOR rn
GROUP BY rn, LEVEL
ORDER BY 1;```

#2: Using Recursive CTE

```WITH x(rn, lvl) AS (
SELECT ROWNUM rn, 1
FROM dual
CONNECT BY LEVEL<=4
UNION ALL
SELECT rn, lvl+1
FROM x
WHERE rn>=lvl+1
)
SELECT rn
FROM x
ORDER BY 1;```

#3: Using Self-Join

```WITH x AS (
SELECT ROWNUM rn
FROM dual
CONNECT BY LEVEL<=4
)
SELECT a.rn
FROM x a JOIN x b ON a.rn>=b.rn
ORDER BY 1;```

Naoto Katayama submitted one more elegant solution using MODEL clause:

#4: Using MODEL clause

```SELECT RN
FROM (SELECT LEVEL rn
FROM DUAL
CONNECT BY LEVEL<=4)
MODEL
PARTITION BY(ROWNUM AS par)
DIMENSION BY(0 AS dummy)
MEASURES(rn)
RULES ITERATE(100) UNTIL ITERATION_NUMBER+1>=rn[0]
(rn[ITERATION_NUMBER]=rn[0])
ORDER BY 1;```

### My Oracle Group on Facebook:

If you like this post, you may want to join my 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.

# Puzzle of the Week #5:

### Find the shortest and longest last names of the employees in each department.

• If two or more employees tie for the shortest or longest name, pick the name that comes first in alphabetical order
• Use hr.employees or scott.emp tables
• Use a single SELECT statement only
• Ideally, the solution should NOT rely on any sub-queries, CTEs  (WITH clause), or inline views
• Exclude unknown (NULL) departments

## Solutions:

#1. Using MIN() KEEP Group Function

```SELECT department_id,
MIN(last_name) KEEP(DENSE_RANK FIRST
ORDER BY LENGTH(last_name)) shortest,
MIN(last_name) KEEP(DENSE_RANK FIRST
ORDER BY LENGTH(last_name) DESC) longest
FROM hr.employees
WHERE department_id IS NOT NULL
GROUP BY department_id```

#2. Using FIRST_VALUE Analytic Function and DISTINCT option

(Credit to Igor Shpungin)

```SELECT DISTINCT department_id,
FIRST_VALUE(last_name) OVER(PARTITION BY department_id
ORDER BY LENGTH(last_name))      shortest,
FIRST_VALUE(last_name) OVER(PARTITION BY department_id
ORDER BY LENGTH(last_name) DESC) longest
FROM hr.employees
WHERE department_id IS NOT NULL
ORDER BY 1```

#3. Using MODEL clause

(Credit to Naoto Katayama)

```SELECT department_id, shortest, longest
FROM hr.employees
WHERE department_id IS NOT NULL
MODEL
RETURN UPDATED ROWS
PARTITION BY (department_id)
DIMENSION BY (
ROW_NUMBER()OVER(PARTITION BY department_id
ORDER BY LENGTH(last_name), last_name) rn1,
ROW_NUMBER()OVER(PARTITION BY department_id
ORDER BY LENGTH(last_name) DESC, last_name) rn2)
MEASURES(last_name,
CAST(NULL AS VARCHAR2(25)) AS shortest,
CAST(NULL AS VARCHAR2(25)) AS longest)
RULES(
shortest[0,0]=MAX(last_name)[1,ANY],
longest [0,0]=MAX(last_name)[ANY,1]
)
ORDER BY department_id```

### My Oracle Group on Facebook:

If you like this post, you may want to join my 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.