## Monday Hiring SQL Puzzle and Lateral View Usage Nuance

Let’s solve a fairly simple SQL problem:

For each department count the number of people hired on Monday

• Use scott.emp table
• Show department number and count columns
• If no employees from a given department was hired on Monday, we should list such
department with 0 in the count column
• Sort the result by the department number

Expected Result:

DEPTNO MON_HIRES
10 0
20 0
30 1

We will start with the in-line scalar subquery approach as it is probably one of the most intuitive:

Strategy #1: In-Line Scalar Subquery

```SELECT deptno, (SELECT COUNT(*)
FROM scott.emp
WHERE deptno=e.deptno
AND TO_CHAR(hiredate, 'DY')='MON') mon_hires
FROM scott.emp e
GROUP BY deptno
ORDER BY 1```

When you only need a single value/column/expression from a correlated subquery, the in-line subquery in SELECT clause works just fine. If we needed more than one: count and let say total salary, we would need to use LATERAL view (or a completely different approach – see below).

Strategy #2: Lateral View

```WITH x AS (
SELECT DISTINCT deptno
FROM scott.emp
)
SELECT x.deptno, m.mon_hires, m.total_sal
FROM x, LATERAL (SELECT COUNT(*) mon_hires, SUM(sal) total_sal
FROM scott.emp e
WHERE e.deptno=x.deptno
AND TO_CHAR(e.hiredate, 'DY')='MON') m
ORDER BY 1```

Result:

DEPTNO MON_HIRES TOTAL_SAL
10 0
20 0
30 1 1250

So far, all is good. I know that some database developers don’t like using table aliases too much and when an opportunity comes they use ANSI standard JOIN syntax with USING clause. Can it be applied in the lateral view?

```WITH x AS (
SELECT DISTINCT deptno
FROM scott.emp
)
SELECT x.deptno, m.mon_hires
FROM x, LATERAL (SELECT COUNT(empno) mon_hires
FROM scott.emp JOIN x USING (deptno)
WHERE TO_CHAR(hiredate, 'DY')='MON') m
ORDER BY 1```

Result:

DEPTNO MON_HIRES
10 1
20 1
30 1

The syntax is correct but the result is apparently not! What happened?

We replaced the WHERE clause condition of e.deptno=x.deptno with the JOIN x USING(deptno) – it first looked legitimate to me until I realized that we have just added an extra join instead of a reference to an external table (CTE x). Essentially, our last (incorrect) query is the same as the following:

```WITH x AS (
SELECT DISTINCT deptno
FROM scott.emp
)
SELECT x.deptno, m.mon_hires
FROM x, LATERAL (SELECT COUNT(*) mon_hires
FROM scott.emp e, x
WHERE e.deptno=x.deptno
AND TO_CHAR(e.hiredate, 'DY')='MON') m
ORDER BY 1```

We simply introduced a new (and unwanted) join on the CTE x and turned the correlated reference (in the WHERE clause) into an old-style joining condition which did not even require the LATERAL view functionality:

```WITH x AS (
SELECT DISTINCT deptno
FROM scott.emp
)
SELECT x.deptno, m.mon_hires
FROM x, (SELECT COUNT(*) mon_hires
FROM scott.emp e, x
WHERE e.deptno=x.deptno
AND TO_CHAR(e.hiredate, 'DY')='MON') m
ORDER BY 1```

Result:

DEPTNO MON_HIRES
10 1
20 1
30 1

The above examples prove that we need to be very careful when mixing up different techniques and new syntax options.

Finally, the last approach will show that only a single scan of the emp table is needed to get the result:

Strategy #3: Conditional Counting

```SELECT deptno, COUNT(DECODE(TO_CHAR(hiredate, 'DY'), 'MON', 1)) mon_hires
FROM scott.emp
GROUP BY deptno
ORDER BY 1```

Result:

DEPTNO MON_HIRES
10 0
20 0
30 1

This is the best and incidentally the shortest solution that once again demonstrates the power of conditional counting (aggregation) right in SELECT clause.

Likewise we can also show the total salary of those hired on Monday:

```SELECT deptno,
COUNT(DECODE(TO_CHAR(hiredate, 'DY'), 'MON', 1)) mon_hires,
SUM(DECODE(TO_CHAR(hiredate, 'DY'), 'MON', sal)) total_sal
FROM scott.emp
GROUP BY deptno
ORDER BY 1```

Result:

DEPTNO MON_HIRES TOTAL_SAL
10 0
20 0
30 1 1250

***

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.

# Mimic ROW_NUMBER function

Write a single SELECT statement that produces the same result as the following one:

```SELECT e.*, ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn
FROM scott.emp e```
• Use a single SELECT statement only.
• Analytic functions are NOT allowed
• Any SQL clauses that use PARTITION BY keywords are NOT allowed

Expected Result:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 1
7839 KING PRESIDENT 17-NOV-81 5000 10 2
7934 MILLER CLERK 7782 23-JAN-82 1300 10 3
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 1
7902 FORD ANALYST 7566 03-DEC-81 3000 20 2
7566 JONES MANAGER 7839 02-APR-81 2975 20 3
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 4
7369 SMITH CLERK 7902 17-DEC-80 800 20 5
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 1
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 2
7900 JAMES CLERK 7698 03-DEC-81 950 30 3
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 4
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 5
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 6

# Solutions:

### Solution #1. Using MATCH_RECOGNIZE clause

Credit to: Naoto Katayama

``````SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,rn
FROM scott.emp
MATCH_RECOGNIZE (
ORDER BY deptno,ename,empno
MEASURES RUNNING COUNT(*) AS rn
ALL ROWS PER MATCH
PATTERN (FIRSTROW NEXTROWS*)
DEFINE
FIRSTROW AS PREV(FIRSTROW.deptno) IS NULL
OR PREV(FIRSTROW.deptno) != FIRSTROW.deptno,
NEXTROWS AS PREV(NEXTROWS.deptno) = NEXTROWS.deptno
)``````

### Solution #2. Using Self-Join with Cartesian Product and GROUP BY

Partial Credit to: Boobal Ganesan

``````SELECT e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e1.comm,e1.deptno,
COUNT(*) rn
FROM scott.emp e1 LEFT OUTER JOIN scott.emp e2
ON e1.deptno = e2.deptno
AND e2.ename || ROWIDTOCHAR(e2.ROWID) <= e1.ename || ROWIDTOCHAR(e1.ROWID)
GROUP BY e1.empno,e1.ename,e1.job,e1.mgr,e1.hiredate,e1.sal,e1.comm,e1.deptno
ORDER BY e1.deptno, COUNT(*)``````

### Solution #3. Using CTE, ROWNUM, and arithmetic formula

``````WITH x AS (
SELECT *
FROM scott.emp
ORDER BY deptno, ename
), y AS (
SELECT deptno, MIN(ROWNUM) min_rn
FROM x
GROUP BY deptno
)
SELECT x.*, ROWNUM-y.min_rn+1 AS rn
FROM x JOIN y ON x.deptno=y.deptno
ORDER BY x.deptno, x.ename``````

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.

# Exact Coin Change Puzzle.

Suppose that you are a sales person at a cash register and you have one purchase to serve before you close. A buyer has to pay X dollars and N cents with bills only (no coins). You have lots of bills of various nomination and limited number of coins: 3 quarters, 9 dimes, 19 nickels, and 4 pennies left in the register. You are required to give the exact change (between 1 and 99 cents) using smallest number of (available) coins.

• Use a single SELECT statement
• The result should return 1 row and 4 columns indicating how many coins of each type to use
• 1 Quarter = 25 cents; 1 Dime = 10 cents; 1 Nickel = 5 cents

Sample result for a change of 63 cents:

```
Quarters      Dimes    Nickels    Pennies
---------- ---------- ---------- ----------
2          1          0          3
```

# Solutions:

## Solution #1: Using Math formula and MODEL clause:

For American coins one can rely on a mathematical formula to get the smallest number of coins for exact change:

Quarters: FLOOR of [Change Amount]/25
Dimes: FLOOR(([Change Amount] – 25*[Quarters])/10)
Nickels: FLOOR(([Change Amount] – 25*[Quarters]-10*[Dimes])/5)
Pennies: [Change Amount] – 25*[Quarters]-10*[Dimes] – 5*[Nickels]

One of the easiest ways to implement this strategy is to employ the MODEL clause:

```WITH m AS (
SELECT 63 AS cents
FROM dual
)
SELECT cents "Change",
Q "Quarters",
D "Dimes",
N "Nickels",
P "Pennies"
FROM m
MODEL
DIMENSION BY(0 AS dummy)
MEASURES(
cents,
CAST(0 AS NUMBER(3)) AS Q,
CAST(0 AS NUMBER(3)) AS D,
CAST(0 AS NUMBER(3)) AS N,
CAST(0 AS NUMBER(3)) AS P
)
RULES (
Q[0]=FLOOR(CENTS[0]/25),
D[0]=FLOOR((CENTS[0]-Q[0]*25)/10),
N[0]=FLOOR((CENTS[0]-Q[0]*25-D[0]*10)/5),
P[0]=(CENTS[0]-Q[0]*25-D[0]*10-N[0]*5)
)```

Result:

Change Quarters Dimes Nickels Pennies
63 2 1 0 3

If we want to extend this solution to see the change combinations for all values from 1 to 99, we will need to change the above solution as follows:

```WITH m AS (
SELECT LEVEL cents
FROM dual
CONNECT BY LEVEL<=99
)
SELECT cents "Change",
Q "Quarters",
D "Dimes",
N "Nickels",
P "Pennies"
FROM m
MODEL
PARTITION BY(ROWNUM AS rn)
DIMENSION BY(0 AS dummy)
MEASURES(
cents,
CAST(0 AS NUMBER(3)) AS Q,
CAST(0 AS NUMBER(3)) AS D,
CAST(0 AS NUMBER(3)) AS N,
CAST(0 AS NUMBER(3)) AS P
)
RULES (
Q[0]=FLOOR(CENTS[0]/25),
D[0]=FLOOR((CENTS[0]-Q[0]*25)/10),
N[0]=FLOOR((CENTS[0]-Q[0]*25-D[0]*10)/5),
P[0]=(CENTS[0]-Q[0]*25-D[0]*10-N[0]*5)
)
ORDER BY 1```

Result:

Change Quarters Dimes Nickels Pennies
1 0 0 0 1
2 0 0 0 2
3 0 0 0 3
4 0 0 0 4
5 0 0 1 0
6 0 0 1 1
7 0 0 1 2
8 0 0 1 3
9 0 0 1 4
10 0 1 0 0
95 3 2 0 0
96 3 2 0 1
97 3 2 0 2
98 3 2 0 3
99 3 2 0 4

## Solution #2: Using Enhanced Math formula:

It’s easy to see that the MOD function is very handy in determining the number of coins other than quarters (the largest):

```WITH a AS (
SELECT 63 cents
FROM dual
)
SELECT a.cents "Change",
FLOOR(a.cents/25) "Quarters",
FLOOR(MOD(a.cents,25)/10) "Dimes",
FLOOR(MOD(MOD(a.cents,25),10)/5) "Nickels",
MOD(MOD(MOD(a.cents,25),10),5) "Pennies"
FROM a```

Alternatively, we can see coin combinations for all change amounts from 1 to 99 cents:

```WITH a AS (
SELECT LEVEL cents
FROM dual
CONNECT BY LEVEL<100
)
SELECT a.cents "Change",
FLOOR(a.cents/25) "Quarters",
FLOOR(MOD(a.cents,25)/10) "Dimes",
FLOOR(MOD(MOD(a.cents,25),10)/5) "Nickels",
MOD(MOD(MOD(a.cents,25),10),5) "Pennies"
FROM a
ORDER BY a.cents```

## Solution #3: Using Cartesian Product and Top Record pattern approach:

If we did not know the exact math formula, we could still count on the brute force approach – go over all possible coin permutations (Cartesian product) that sum up to the required total amount and then chose the combination with the fewest number of coins (top record pattern):

```WITH r AS (
SELECT LEVEL-1 n
FROM dual
CONNECT BY LEVEL<=20
), x AS (
SELECT q.n "Quarters", d.n "Dimes", n.n "Nickels", p.n "Pennies",
RANK() OVER(ORDER BY q.n + d.n + n.n + p.n) rk
FROM r q, r d, r n, r p
WHERE q.n<=3
AND d.n<=9
AND n.n<=19 --not needed
AND p.n<=4
AND q.n*25 + d.n*10 + n.n*5 + p.n = 63 --amount of change
)
SELECT "Quarters", "Dimes", "Nickels", "Pennies"
FROM x
WHERE rk=1```

If we want to extend this solution to see the change combinations for all values from 1 to 99, we will need to change the above solution as follows:

```WITH r AS ( -- this range is to be reused 5 times in this query
SELECT LEVEL-1 n
FROM dual
CONNECT BY LEVEL<=100
), x AS (
SELECT c.n "Change", q.n "Quarters", d.n "Dimes",
n.n "Nickels", p.n "Pennies",
RANK() OVER(PARTITION BY c.n ORDER BY q.n + d.n + n.n + p.n) rk
FROM r q, r d, r n, r p, r c
WHERE q.n<=3
AND d.n<=9
AND n.n<=19 --now it is needed
AND p.n<=4  AND q.n*25 + d.n*10 + n.n*5 + p.n = c.n --amount of change
AND c.n>0
)
SELECT "Change", "Quarters", "Dimes", "Nickels", "Pennies"
FROM x
WHERE rk=1
ORDER BY 1```

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.

# 2018 Puzzle of the Week #1:

For a given text string, find the first (from the beginning) longest sub-string that does not have repeating characters.

# Solutions:

## Solution #1: Using CONNECT BY clause (for range generation), REGEXP_COUNT, and RANK() functions:

```WITH w AS (
SELECT 'arkansas' AS word
FROM dual
), r AS (
SELECT ROWNUM rn
FROM w
CONNECT BY LEVEL<=LENGTH(word)
), x AS (
SELECT SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1) substr,
RANK() OVER(ORDER BY r2.rn - r1.rn DESC, r1.rn) rk
FROM r r1, r r2, w
WHERE r1.rn<=r2.rn
AND REGEXP_COUNT(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1), '(.).*\1') = 0
)
SELECT substr
FROM x
WHERE rk=1```

Result of execution in Oracle Live SQL client:

SUBSTR
rkans

## Solution #2: Using CONNECT BY clause (for range generation), REGEXP_LIKE, and MAX() KEEP functions:

```WITH w AS (
SELECT 'arkansas' AS word
FROM dual
), r AS (
SELECT ROWNUM rn
FROM w
CONNECT BY LEVEL<=LENGTH(word)
)
SELECT MAX(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1))
KEEP(DENSE_RANK FIRST ORDER BY r2.rn - r1.rn DESC, r1.rn) substr
FROM r r1, r r2, w
WHERE r1.rn<=r2.rn
AND NOT REGEXP_LIKE(SUBSTR(w.word, r1.rn, r2.rn - r1.rn + 1), '(.).*\1')```

## Solution #3: Using CONNECT BY clause (twice), LATERAL view, REGEXP_COUNT, and RANK() functions:

```WITH w AS (
SELECT 'arkansas' AS word
FROM dual
), s AS (
SELECT SUBSTR(word, LEVEL) word, LEVEL rn
FROM w
CONNECT BY LEVEL<=LENGTH(word)
)
SELECT MAX(x.substr)
KEEP(DENSE_RANK FIRST ORDER BY LENGTH(x.substr) DESC, s.rn) substr
FROM s, LATERAL(SELECT SUBSTR(s.word, 1, LEVEL) substr
FROM dual
CONNECT BY LEVEL<=LENGTH(s.word)) x
WHERE REGEXP_COUNT(x.substr, '(.).*\1') = 0```

## Solution #4: Using XMLTable function (for range generation), Correlated subquery with COUNT(DISTINCT), and MAX() KEEP function:

```WITH w AS (
SELECT 'arkansas' AS word
FROM dual
), r AS (
SELECT ROWNUM rn, word
FROM w, XMLTABLE('for \$i in 1 to \$N cast as xs:integer return \$i'
PASSING LENGTH(w.word) AS N) x
)
SELECT MAX(SUBSTR(r1.word, r1.rn, r2.rn - r1.rn + 1))
KEEP(DENSE_RANK FIRST ORDER BY r2.rn - r1.rn DESC, r1.rn) substr
FROM r r1, r r2
WHERE r1.rn<=r2.rn
AND r2.rn - r1.rn + 1 =
(SELECT COUNT(DISTINCT SUBSTR(SUBSTR(r1.word, r1.rn, r2.rn - r1.rn + 1),
LEVEL, 1))
FROM dual
CONNECT BY LEVEL<=r2.rn - r1.rn + 1
)```

## Solution #5: Using CONNECT BY, Recursive CTE, INSTR, SUBSTR, and MAX() KEEP functions:

```WITH w AS (
SELECT 'arkansas' word
FROM dual
), s(sub, word, lvl, rn) AS (
SELECT SUBSTR(word, LEVEL, 1), SUBSTR(word, LEVEL) word, 1, ROWNUM
FROM w
CONNECT BY SUBSTR(word, LEVEL) IS NOT NULL
UNION ALL
SELECT SUBSTR(word, 1, lvl+1), word, lvl+1, ROWNUM
FROM s
WHERE LENGTH(SUBSTR(word, 1, lvl+1))=lvl+1
AND INSTR(sub, SUBSTR(word, lvl+1, 1))=0
)
SELECT MAX(sub) KEEP (DENSE_RANK FIRST ORDER BY lvl DESC, rn) substr
FROM s```

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.

## Three Strategies for replacing multiple spaces with single ones.

Sometimes, we get to work with a text that has multiple sequential space characters that we don’t need, and hence need to remove. There are many ways to accomplish the task in pure Oracle SQL. Below, you can find 3 of them that I believe are worth mentioning.

We will be removing undesired spaces from the following character string:

`Three   Strategies    for    replacing    multiple         spaces`

## Strategy #1: Regular Expressions

Of course, every time we need to process some text data, Regular Expressions come to mind. Here, the choice is very natural and straightforward:

```WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
)
SELECT REGEXP_REPLACE(text, '[ ]+',' ') text
FROM x
```

Result:

```TEXT
----------------------------------------------
Three Strategies for replacing multiple spaces
```

Regular expression [ ]+    finds all occurrences of sequential spaces and regexp_replace function substitutes each of such occurrences with a single space.

## Strategy #2: Triple Replace

```WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
)
SELECT REPLACE(REPLACE(REPLACE(text, ' ', '| '), ' |'), '|') text
FROM x
```

Result:

```TEXT
----------------------------------------------
Three Strategies for replacing multiple spaces
```

To see how this trick works, let’s break it down in 3 steps:

Step 1: Replace Spaces with Pipe-Space combination:

```WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
)
SELECT REPLACE(text, ' ', '| ') text
FROM x
```

Result:

```Three| | | Strategies| | | | for| | | | replacing| | | | multiple| | | | | | | | | spaces
```

Step 2: Remove all Space-Pipe combinations

```WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
)
SELECT REPLACE(REPLACE(text, ' ', '| '), ' |') text
FROM x
```

Result:

```TEXT
---------------------------------------------------
Three| Strategies| for| replacing| multiple| spaces
```

Note, that we first replaced each space with PIPE-SPACE sequence and then removed the opposite order sequence SPACE-PIPE, which left us with non-repeated occurrences of PIPE-Space combinations.

Step 3: Remove PIPE characters

```WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
)
SELECT REPLACE(REPLACE(REPLACE(text, ' ', '| '), ' |'), '|') text
FROM x
```

Result:

```TEXT
----------------------------------------------
Three Strategies for replacing multiple spaces
```

## Strategy #3: Recursive WITH Clause

```WITH x AS (
SELECT 'Three Strategies for replacing multiple spaces' AS text
FROM dual
), y(text) AS (
SELECT REPLACE(x.text, '  ', ' ')
FROM x
UNION ALL
SELECT REPLACE(y.text, '  ', ' ')
FROM y
WHERE INSTR(y.text, '  ')>0
)
SELECT text
FROM y
WHERE INSTR(y.text, '  ')=0```

Result:

```TEXT
----------------------------------------------
Three Strategies for replacing multiple spaces```

The idea is to replace two-space combination with a single space until no more two-space sequence will be left in the text.

## Final thoughts

What will change if we need to process not a single text value but a table/collection?

Let’s examine how each of the above strategies will work:

### Strategy #1: Regular Expressions

```WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
UNION ALL
SELECT 'One    more    string with     spaces   '
FROM dual
)
SELECT REGEXP_REPLACE(text, '[ ]+',' ') text
FROM x
```

Result:

```TEXT
-----------------------------------------------
Three Strategies for replacing multiple spaces
One more string with spaces
```

### Strategy #2: Triple Replace

```WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
UNION ALL
SELECT 'One    more    string with     spaces   '
FROM dual
)
SELECT REPLACE(REPLACE(REPLACE(text, ' ', '| '), ' |'), '|') text
FROM x
```

Result:

```TEXT
-----------------------------------------------
Three Strategies for replacing multiple spaces
One more string with spaces
```

So far, so good.

### Strategy #3: Recursive WITH Clause

```WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
UNION ALL
SELECT 'One    more    string with     spaces   '
FROM dual
), y(text) AS (
SELECT REPLACE(x.text, '  ', ' ')
FROM x
UNION ALL
SELECT REPLACE(y.text, '  ', ' ')
FROM y
WHERE INSTR(y.text, '  ')>0
)
SELECT text
FROM y
WHERE INSTR(y.text, '  ')=0
```

Result:

```TEXT
-----------------------------------------------
One more string with spaces
Three Strategies for replacing multiple spaces
```

All is good except for the order – the line with the smaller number of “double” spaces came first as it was first cleaned up. To preserve the original sort order we will need to make a change:

```WITH x AS (
SELECT 'Three   Strategies    for    replacing    multiple         spaces'  AS text
FROM dual
UNION ALL
SELECT 'One    more    string with     spaces   '
FROM dual
), y(text, rn) AS (
SELECT REPLACE(x.text, '  ', ' '), ROWNUM
FROM x
UNION ALL
SELECT REPLACE(y.text, '  ', ' '), y.rn
FROM y
WHERE INSTR(y.text, '  ')>0
)
SELECT text
FROM y
WHERE INSTR(y.text, '  ')=0
ORDER BY rn
```

Result:

```TEXT
-----------------------------------------------
Three Strategies for replacing multiple spaces
One more string with spaces
```

Now, it’s all good!

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

## How to generate a list of first N binary numbers in Oracle SQL?

In my recent post I showed how to convert a decimal number (i.e. an integer) into a binary string. We can build upon that technique to answer the question:

```WITH x AS (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL<=50
)
SELECT x.N, y.bin
FROM x, LATERAL (SELECT LISTAGG(SIGN(BITAND(x.N, POWER(2,LEVEL-1))),'')
WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=x.N) y
```

Note the LATERAL keyword (Oracle 12c new feature) that enables us to reference “x” in the inline view “y”. In pre-12c world, we would have to use TABLE/CAST/MULTISET function composition to achieve the same result:

```WITH x AS (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL<=50
)
SELECT x.N, y.column_value bin
FROM x, TABLE(CAST(MULTISET(
SELECT LISTAGG(SIGN(BITAND(x.N, POWER(2,LEVEL-1))),'')
WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=x.N) AS sys.odcivarchar2list)) y
```

The idea used in the following query is based on a totally different approach. It builds a string of “0”s and “1”s in a loop until its length reaches a desired value:

```WITH x(v, n) AS (
SELECT column_value, 1
FROM TABLE(sys.odcivarchar2list('0','1'))
UNION ALL
SELECT x.v || t.column_value, x.n+1
FROM TABLE(sys.odcivarchar2list('0','1')) t JOIN x on LENGTH(x.v)=n
WHERE n<=CEIL(LOG(2,50))
), y AS (
SELECT NVL(LTRIM(x.v,'0'),'0') bin, ROWNUM-1 dec
FROM x
WHERE n=(SELECT MAX(n) FROM x)
)
SELECT *
FROM y
WHERE dec<=50
```

To better understand the above query, try the following one:

```SELECT *
FROM TABLE(sys.odcivarchar2list('0','1')),
TABLE(sys.odcivarchar2list('0','1')),
TABLE(sys.odcivarchar2list('0','1')),
TABLE(sys.odcivarchar2list('0','1'))
```

If we put enough tables in the Cartesian product and concatenate all column_value columns in a single character string expression, we will achieve our goal. The challenge with this approach is to dynamically change the number of the tables in the FROM clause. This can be simulated in the recursive WITH clause by repeatedly adding more and more collections of bits (0 and 1).

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

## Puzzle of the Week #20:

Produce the historical highest/lowest salary report that should comply with the following requirements:

• Use Single SELECT statement only
• Only employees who was paid the highest or lowest salary in their respective department at the moment of hiring should be selected
• Show name, date of hire, department number, job title, salary table (emp) columns and two additional calculated columns/flags: min_flag and max_flag to indicate that the employee was hired with the min/max salary in their respective department as of the time of hiring.
• If two or more employees in the same department are paid the same max/min salary, only the one who was hired first should be picked for the report.
• The query should work in Oracle 11g.

### Expected Result:

#1. Using Common Table Expression (CTE) or Recursive WITH clause

```WITH y AS (
SELECT ename, job, deptno, hiredate, sal,
ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY hiredate) rn
FROM emp
), x (ename, job, deptno, hiredate, sal, min_sal, max_sal, min_flag, max_flag, rn) AS (
SELECT ename, job, deptno, hiredate, sal, sal, sal, 1, 1, 1
FROM y
WHERE rn=1
UNION ALL
SELECT y.ename, y.job, y.deptno, y.hiredate, y.sal,
LEAST(x.min_sal, y.sal), GREATEST(x.max_sal, y.sal),
CASE WHEN y.sal<x.min_sal THEN 1 END,
CASE WHEN y.sal>x.max_sal THEN 1 END, y.rn
FROM y JOIN x ON y.deptno=x.deptno AND y.rn=x.rn+1
)
SELECT ename, job, deptno, hiredate, sal, min_flag, max_flag
FROM x
WHERE 1 IN (min_flag, max_flag)
ORDER BY deptno, hiredate;```

#2. Using Cumulative Analytic Functions MIN, MAX, and ROW_NUMBER

```WITH x AS (
SELECT ename, job, deptno, hiredate, sal,
MIN(sal)OVER(PARTITION BY deptno ORDER BY hiredate) min_sal,
MAX(sal)OVER(PARTITION BY deptno ORDER BY hiredate) max_sal,
ROW_NUMBER()OVER(PARTITION BY deptno, sal ORDER BY hiredate) rn
FROM emp
)
SELECT ename, job, deptno, hiredate, sal,
DECODE(sal, min_sal, 1) min_flag,
DECODE(sal, max_sal, 1) max_flag
FROM x
WHERE sal IN (min_sal, max_sal)
AND rn=1;```

#3. Using Cumulative Analytic Functions MIN, MAX, and COUNT

```WITH x AS (
SELECT ename, job, deptno, hiredate, sal,
CASE WHEN MIN(sal)OVER(PARTITION BY deptno ORDER BY hiredate)=sal
AND COUNT(*)OVER(PARTITION BY deptno, sal ORDER BY hiredate)=1 THEN 1
END min_flag,
CASE WHEN MAX(sal)OVER(PARTITION BY deptno ORDER BY hiredate)=sal
AND COUNT(*)OVER(PARTITION BY deptno, sal ORDER BY hiredate)=1 THEN 1
END max_flag
FROM emp
)
SELECT *
FROM x
WHERE 1 IN (min_flag, max_flag);```

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

## Puzzle of the Week #16:

With a single SELECT statement find the biggest prime factor of a given integer value (N).

### Expected Result:

```--For N=100:

Biggest Prime Factor
--------------------
5

--For N=52:

Biggest Prime Factor
--------------------
13

--For N=21:

Biggest Prime Factor
--------------------
7
```

## Solutions

### #1: Using CTE (recursive WITH)

```WITH input AS (
SELECT &N n
FROM dual
), x(num, flag) AS (
SELECT 2, CASE WHEN MOD(n, 2)=0 THEN 1 ELSE 0 END AS flag
FROM input
UNION ALL
SELECT x.num+1, CASE WHEN MOD(i.n, x.num+1)=0 THEN 1 ELSE 0 END
FROM input i, x
WHERE x.num+1<=i.n
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.num,LEVEL)=0
CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;

Enter value for n: 100
old   2: SELECT &N n
new   2: SELECT 100 n

Biggest Prime Factor
--------------------
5

SQL> /
Enter value for n: 52
old   2: SELECT &N n
new   2: SELECT 52 n

Biggest Prime Factor
--------------------
13

SQL> /
Enter value for n: 21
old   2: SELECT &N n
new   2: SELECT 21 n

Biggest Prime Factor
--------------------
7

```

### #2: Using CONNECT BY clause , version 1

```WITH input AS (
SELECT &N n
FROM dual
), x AS (
SELECT LEVEL num
FROM input i
WHERE MOD(i.N, LEVEL)=0
CONNECT BY LEVEL<=i.N
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.num,LEVEL)=0
CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;```

### #3: Using CONNECT BY clause, version 2

```WITH input AS (
SELECT &N n
FROM dual
), range AS (
SELECT LEVEL num
FROM input i
CONNECT BY LEVEL <= i.N
), x AS(
SELECT r1.num
FROM range r1, range r2, input i
WHERE MOD(i.N, r1.num)=0
GROUP BY r1.num
HAVING COUNT(CASE WHEN MOD(r1.num, r2.num)=0 THEN 1 END)=2
)
SELECT MAX(num) "Biggest Prime Factor"
FROM x;
```

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