# 800 Phone Puzzle

For a given 800 phone number (like 1-800-123-4567) find all number-letter representations.

• Use a single SELECT statement only.
• Only last 4 digits of the phone number have to be replaced with letters.
• Exactly 1 letter (out of 4) must be vowel,  the rest – consonant
• The following table shows all possible mappings:
Digit Maps to
1 1
2 A, B, C
3 D, E, F
4 G, H, I
5 J, K, L
6 M, N, O
7 P, Q, R, S
8 T, U, V
9 W, X, Y, Z
0 0

# Solutions:

Essentially, all solutions below share the same idea of generating the letter based phone numbers. The differences are in a way the mapping CTE is created and a way to limit the number of vowels to 1.

### Solution #1. Compact form of creating the map CTE with recursive check for the vowels:

``````WITH map AS (
SELECT digit, letter, '4357' phone
FROM TABLE(sys.odcivarchar2list('00','11','2ABC','3DEF','4GHI',
'5JKL','6MNO','7PQRS','8TUV','9WXYZ')) t,
LATERAL(SELECT SUBSTR(t.column_value,1,1) digit,
SUBSTR(t.column_value,1+LEVEL,1) letter
FROM dual
CONNECT BY SUBSTR(t.column_value,1+LEVEL,1) IS NOT NULL) x
), res(str, lvl, phone,has_vowel) AS (
SELECT letter, 1, phone,
CASE WHEN letter IN ('A','E','I','O','U') THEN 1 ELSE 0 END
FROM map
WHERE SUBSTR(phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1, res.phone,
CASE WHEN letter IN ('A','E','I','O','U')
OR res.has_vowel=1 THEN 1 ELSE 0 END
FROM res JOIN map ON SUBSTR(res.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(res.phone)
AND NOT (letter IN ('A','E','I','O','U') AND res.has_vowel=1)
)
SELECT '1-800-123-' || str phone
FROM res
WHERE lvl=LENGTH(phone)
AND has_vowel=1``````

### Solution #2. Using more efficient way of creating the map CTE :

```WITH x AS (
SELECT ROWNUM-1 digit,COLUMN_VALUE letters
FROM TABLE(sys.odcivarchar2list('0','1','ABC','DEF','GHI','JKL',
'MNO','PQRS','TUV','WXYZ'))
), map AS (
SELECT digit, SUBSTR(letters, level, 1) letter, '4357' phone
FROM x
CONNECT BY SUBSTR(letters, level, 1) IS NOT NULL
AND PRIOR digit = digit
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
), res(str, lvl, phone,has_vowel) AS (
SELECT letter, 1, phone,
CASE WHEN letter IN ('A','E','I','O','U') THEN 1 ELSE 0 END
FROM map
WHERE SUBSTR(phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1, res.phone,
CASE WHEN letter IN ('A','E','I','O','U')
OR res.has_vowel=1 THEN 1 ELSE 0 END
FROM res JOIN map ON SUBSTR(res.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(res.phone)
AND NOT (letter IN ('A','E','I','O','U') AND res.has_vowel=1)
)
SELECT '1-800-123-' || str phone
FROM res
WHERE lvl=LENGTH(phone)
AND has_vowel=1```

### Solution #3. Much more efficient way of creating the map CTE and using Regular Expression to limit the vowels :

```WITH d AS (
SELECT LEVEL+1 n, CASE WHEN LEVEL+1 IN (7,9) THEN 4 ELSE 3 END cnt,
'4357' phone
FROM dual
CONNECT BY LEVEL<=8
), a AS (
SELECT CHR(ASCII('A')+LEVEL-1) letter, ROWNUM rn
FROM dual
CONNECT BY CHR(ASCII('A')+LEVEL-1)<='Z'
), x AS (
SELECT n,
1+NVL(SUM(cnt) OVER(ORDER BY n ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 PRECEDING),0) c1,
SUM(cnt) OVER(ORDER BY n) c2,
phone
FROM d
), map AS (
SELECT n digit, letter, x.phone
FROM x JOIN a ON a.rn BETWEEN x.c1 AND x.c2
UNION
SELECT ROWNUM-1, TO_CHAR(ROWNUM-1), x.phone
FROM x
WHERE ROWNUM<=2
), res(str, lvl) AS (
SELECT letter, 1
FROM map
WHERE SUBSTR(map.phone,1,1)=TO_CHAR(map.digit)
UNION ALL
SELECT res.str || letter, res.lvl+1
FROM res JOIN map ON SUBSTR(map.phone, res.lvl+1,1)=TO_CHAR(map.digit)
WHERE res.lvl+1<=LENGTH(map.phone)
AND REGEXP_COUNT(res.str || letter,'[AEIOU]')<=1
)
SELECT str phone
FROM res
WHERE lvl=4
AND REGEXP_COUNT(str,'[AEIOU]')=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.

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

# Calculate Mutual Funds’ Performance

For a given table fund_performance (see the CREATE TABLE statement below), calculate each fund’s performance over the 6-month period from Jan-2016 till Jun-2016.

• Use a single SELECT statement
• Performance is calculated as a multiplication of all the months’ performance rates for the given time frame
• The solution should work for any time frame, so treat from-month and to-month as query parameters
• DDL command:
• ```CREATE TABLE fund_performance AS
SELECT 1 fund_id, '2016-01' perf_month, 1.05 perf_rate
FROM dual
UNION ALL
SELECT 1, '2016-02', 1.02 FROM dual UNION ALL
SELECT 1, '2016-03', 0.92 FROM dual UNION ALL
SELECT 1, '2016-04', 1.01 FROM dual UNION ALL
SELECT 1, '2016-05', 1.04 FROM dual UNION ALL
SELECT 1, '2016-06', 0.95 FROM dual UNION ALL
SELECT 2, '2016-01', 1.04 FROM dual UNION ALL
SELECT 2, '2016-02', 1.03 FROM dual UNION ALL
SELECT 2, '2016-03', 0.98 FROM dual UNION ALL
SELECT 2, '2016-04', 1.04 FROM dual UNION ALL
SELECT 2, '2016-05', 1.01 FROM dual UNION ALL
SELECT 2, '2016-06', 0.98 FROM dual;
```

Expected Result:

FUND_ID Cumulative Performance
1 0.98
2 1.08

# Solutions:

## Solution #1: Using Math Formula (Sum of Logs = Log of Product)

```SELECT fund_id, ROUND(EXP(SUM(LN(perf_rate))),2) "Cumulative Performance"
FROM fund_performance
WHERE perf_month BETWEEN '2016-02' AND '2016-05'
GROUP BY fund_id
ORDER BY 1```

## Solution #2: Using Dynamic XML Query with XMLTYPE

(Credit to: Katayama Naoto)

```SELECT fund_id,
ROUND(TO_NUMBER(EXTRACTVALUE(XMLTYPE(
dbms_xmlgen.getxml('SELECT '|| LISTAGG(perf_rate,'*')
WITHIN GROUP(ORDER BY perf_month)||' C
FROM dual')),'/ROWSET/ROW/C')),2) AS "cumulative performance"
FROM fund_performance
WHERE perf_month BETWEEN '2016-01' AND '2016-06'
GROUP BY fund_id
ORDER BY 1
```

## Solution #3: Using Dynamic XML with XMLQUERY

(Credit to: Boobal Ganesan)

```SELECT fund_id,
ROUND(TO_NUMBER(XMLQUERY((LISTAGG(perf_rate,'*')
WITHIN GROUP(ORDER BY fund_id)) RETURNING CONTENT)),2) "cumulative performance"
FROM fund_performance
WHERE perf_month BETWEEN '2016-01' AND '2016-06'
GROUP BY fund_id
```

## Solution #4: Using Model Clause with 2 measures

(Credit to: Katayama Naoto)

```WITH x AS (
SELECT fund_id, cump, flag
FROM fund_performance
WHERE perf_month BETWEEN '2016-01' AND '2016-06'
MODEL
PARTITION BY (fund_id)
DIMENSION BY (ROW_NUMBER()OVER(PARTITION BY fund_id ORDER BY perf_month) AS N)
MEASURES(perf_rate,
CAST(0 AS NUMBER) AS cump,
CAST(0 AS NUMBER) AS flag)
RULES(
cump[ANY] ORDER BY N = perf_rate[CV(N)] * NVL(cump[CV(N)-1],1),
flag[ANY] ORDER BY N = NVL2(perf_rate[CV(N)+1],0,1)
)
)
SELECT fund_id, ROUND(cump,2) "Cumulative Performance"
FROM x
WHERE flag=1
ORDER BY fund_id
```

## Solution #5: Using Model clause with 1 measure

```WITH d AS (
SELECT fund_id, perf_month, perf_rate,
RANK()OVER(PARTITION BY fund_id ORDER BY perf_month DESC) rk
FROM fund_performance
WHERE perf_month BETWEEN '2016-01' AND '2016-06'
), x AS (
SELECT *
FROM d
MODEL
PARTITION BY (fund_id)
DIMENSION BY (ROW_NUMBER()OVER(PARTITION BY fund_id ORDER BY perf_month) AS N)
MEASURES     (perf_rate, rk, CAST(0 AS NUMBER) AS cump)
RULES        (cump[ANY] ORDER BY N = perf_rate[CV(N)] * NVL(cump[CV(N)-1],1) )
)
SELECT fund_id, ROUND(cump,2) "Cumulative Performance"
FROM x
WHERE rk=1
ORDER BY fund_id
```

## Solution #6: Using Recursive CTE

```WITH d AS (
SELECT fund_id, perf_rate,
ROW_NUMBER()OVER(PARTITION BY fund_id ORDER BY perf_month) rn,
COUNT(*)OVER(PARTITION BY fund_id) cnt
FROM fund_performance
WHERE perf_month BETWEEN '2016-01' AND '2016-06'
), x(fund_id, cum_perf, rn, cnt) AS (
SELECT fund_id, perf_rate, 1, cnt
FROM d
WHERE rn=1
UNION ALL
SELECT x.fund_id, x.cum_perf*d.perf_rate, d.rn, d.cnt
FROM x JOIN d ON x.fund_id=d.fund_id
AND x.rn+1=d.rn
)
SELECT fund_id, ROUND(cum_perf,2) "Cumulative Performance"
FROM x
WHERE rn=cnt
```

## Solution #7: Using 12c new Function based WITH clause

(Credit to: Katayama Naoto)

```WITH
FUNCTION product(list IN sys.odcinumberlist) RETURN NUMBER IS
v_result NUMBER DEFAULT 1;
BEGIN
FOR i IN list.FIRST .. list.LAST LOOP
v_result := v_result * list(i);
END LOOP;
RETURN v_result;
END;
SELECT fund_id, product(CAST(COLLECT(perf_rate) AS sys.odcinumberlist)) AS "Cumulative Performance"
FROM fund_performance
GROUP BY fund_id
ORDER BY 1```

You can execute first 6 of 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.