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

# Puzzle of the Week #12

With a single SELECT statement produce a list of first 10 prime numbers above a given number of N.

Expected Result: (for N=15)

```     Prime
----------
17
19
23
29
31
37
41
43
47
53

10 rows selected.```

Expected Result: (for N=50)

```     Prime
----------
53
59
61
67
71
73
79
83
89
97

10 rows selected.```

### Solutions:

#1: Liming number of found prime numbers in CTE (Recursive WITH clsue)

```WITH y AS (
SELECT 500 fromN
FROM dual
), x (n, cnt, flag) AS (
SELECT fromN,
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN),
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN)
FROM y
UNION ALL
SELECT x.n+1, (SELECT x.cnt+CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1),
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1)
FROM x
WHERE x.cnt```

#2: Limiting number of found prime numbers outside of CTE (Recursive WITH clsue)

```WITH y AS (
SELECT 50 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN,
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN)
FROM y
UNION ALL
SELECT x.n+1, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1) FROM x WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
AND ROWNUM<=10;

PRIME
----------
53
59
61
67
71
73
79
83
89
97

10 rows selected.

Elapsed: 00:00:00.02```

#3: Using TABLE and MULTISET functions

```WITH y AS (
SELECT 16 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN, column_value flag
FROM y, TABLE(CAST(MULTISET(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END flag
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN) AS sys.odcinumberlist))
UNION ALL
SELECT x.n+1, column_value flag
FROM x, TABLE(CAST(MULTISET(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END flag
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1) AS sys.odcinumberlist))  WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
AND ROWNUM<=10;

PRIME
----------
17
19
23
29
31
37
41
43
47
53

10 rows selected.

Elapsed: 00:00:00.12
```

#4: Using LATERAL views

```WITH y AS (
SELECT 16 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN, is_prime
FROM y, LATERAL (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END is_prime
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN)
UNION ALL
SELECT x.n+1, is_prime
FROM x, LATERAL (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END is_prime
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1) WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
AND ROWNUM<=10;

PRIME
----------
17
19
23
29
31
37
41
43
47
53

10 rows selected.

Elapsed: 00:00:00.11
```

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

## Interview Question

For each of the following salary ranges select two randomly chosen employees:

```0-999
1000-1999
2000-2999
3000+```

## Expected Result:

```ENAME             SAL RANGE
---------- ---------- ---------
SCOTT            3000 3000+
FORD             3000 3000+
BLAKE            2850 2000-2999
CLARK            2450 2000-2999
TURNER           1500 1000-1999
MILLER           1300 1000-1999
JAMES             950 0-999
SMITH             800 0-999```

## Solution:

```WITH x AS (
SELECT ename, sal,
CASE WHEN sal>=3000 THEN '3000+'
WHEN sal>=2000 THEN '2000-2999'
WHEN sal>=1000 THEN '1000-1999'
ELSE                '0-999'
END as range,
ROW_NUMBER() OVER(PARTITION BY DECODE(GREATEST(sal, 3000), sal, 0, 1) +
DECODE(GREATEST(sal, 2000), sal, 0, 1) +
DECODE(GREATEST(sal, 1000), sal, 0, 1)
ORDER BY DBMS_RANDOM.VALUE) rn
FROM emp
)
SELECT ename, sal, range
FROM x
WHERE rn<=2
ORDER BY sal DESC```

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

Produce the Employee Roll Report that satisfies the following list of requirements:

• Use single SELECT statement
• Single column “Names” should have a list of the employee names separated by comma
• The maximum size of the values in the “Names” column should be 23
• The report should have as few rows as possible
• All the employee names should be concatenated in the alphabetical order

### Expected Result:

(the Length column is added for length verification only)

```Names                                        Length
---------------------------------------- ----------
FORD,JAMES,JONES,KING                            21
MARTIN,MILLER,SCOTT                              19
SMITH,TURNER,WARD                                17```

### Solutions

#1 – Using Recursive WITH clause (Common Table Expression) – to contactenate names

```WITH e AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn, 23 AS maxlen
FROM emp
), x (rn, txt, grp) AS (
SELECT 1, CAST(ename AS VARCHAR2(100)), 1
FROM e
WHERE rn=1
UNION ALL
SELECT e.rn,
CASE WHEN LENGTH(x.txt||','||e.ename)>e.maxlen THEN e.ename
ELSE x.txt||','||e.ename
END,
CASE WHEN LENGTH(x.txt||','||e.ename)>e.maxlen THEN x.grp+1
ELSE x.grp
END
FROM e JOIN x ON e.rn=x.rn+1
)
SELECT MAX(txt) "Names", LENGTH(MAX(txt)) "Length"
FROM x
GROUP BY grp
ORDER BY grp;

Names                         Length
------------------------- ----------
FORD,JAMES,JONES,KING             21
MARTIN,MILLER,SCOTT               19
SMITH,TURNER,WARD                 17

```

#2 – Using Recursive WITH clause (Common Table Expression) – to group names AND LISTAGG function

```WITH t (ename, len, rn) AS (
SELECT ename, LENGTH(ename) + 1, ROW_NUMBER() OVER(ORDER BY ename)
FROM emp
), r (ename, running_len, rn, gp) AS (
SELECT ename, len, rn, 1
FROM t
WHERE rn = 1
UNION ALL
SELECT t.ename,
CASE WHEN t.len > 24 - r.running_len THEN t.len ELSE r.running_len + t.len END,
t.rn,
r.gp + CASE WHEN t.len > 24 - r.running_len THEN 1 ELSE 0 END
FROM t JOIN r ON t.rn = r.rn + 1
)
SELECT LISTAGG(ename, ',') WITHIN GROUP(ORDER BY rn) AS "Names", MAX(running_len) - 1 AS "Length"
FROM r
GROUP BY gp
ORDER BY gp
/
```

#3: Using Recursive WITH clause (CTE) – to group names in a different way

```WITH data (ename, grp, pass) AS (
SELECT ename,
CASE WHEN SUM(LENGTH(ename) + 1) OVER(ORDER BY  ename) - 1 <= 23
THEN 1
ELSE 0
END, 1
FROM emp
UNION ALL
SELECT ename,
CASE WHEN SUM(LENGTH(ename) + 1) OVER (ORDER BY  ename) - 1 <= 23
THEN 1
END, pass + 1
FROM data
WHERE (grp = 0 AND pass = 1) OR grp IS NULL
), x AS (
SELECT LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS names, pass
FROM data
WHERE grp = 1
GROUP BY pass
)
SELECT names "Names", LENGTH(names) AS "Length"
FROM x
ORDER BY 1;```

#4: Using XMLAGG with Regular Expressions

```WITH t AS (
SELECT TRIM(',' FROM XMLAGG(xmlelement(e, ename||',') ORDER BY ename).EXTRACT('//text()')) AS txt
FROM  emp
), x AS (
SELECT LEVEL AS l,
TRIM(',' FROM TRIM(REGEXP_SUBSTR(txt,'.{1,23}(,|\$)',1,LEVEL))) AS names
FROM t
CONNECT BY TRIM(',' FROM TRIM(REGEXP_SUBSTR(txt,'.{1,23}(,|\$)',1,LEVEL))) IS NOT NULL
)
SELECT names "Names", LENGTH(names) "Length"
FROM x
/
```

#5: Using LISTAGG with Regular Expressions

```WITH  x AS (
SELECT LISTAGG (ename, ',') WITHIN GROUP (ORDER BY 1) str
FROM emp
)
SELECT RTRIM(REGEXP_SUBSTR (str, '.{1,23}(,|\$)', 1, LEVEL), ',')  "Names",
LENGTH(RTRIM(REGEXP_SUBSTR (str, '.{1,23}(,|\$)', 1, LEVEL), ',')) "Length"
FROM x
CONNECT BY RTRIM(REGEXP_SUBSTR (str, '.{1,23}(,|\$)', 1, LEVEL), ',') IS NOT NULL```

#6: Using MODEL clause for grouping names

```WITH m AS (
SELECT i, ename, grp, len, prevlen
FROM emp
MODEL
DIMENSION BY (ROW_number() OVER (ORDER BY  ename) AS i)
MEASURES
(
ename AS ename,
CAST('' AS VARCHAR2(24)) AS names,
0 AS grp,
0 AS len,
0 AS prevlen
)
RULES
(
len[i] = LENGTH(ename[CV()]),
prevlen[i] = CASE WHEN (CASE WHEN NVL(prevlen[CV()-1],0) = 0 THEN NVL(len[CV()-1],0)
ELSE NVL(prevlen[CV()-1],0) + 1 +  NVL(len[CV()-1],0)
END) > 23
THEN NVL(len[CV()-1],0)
ELSE CASE WHEN NVL(prevlen[CV()-1],0) = 0 THEN NVL(len[CV()-1],0)
ELSE NVL(prevlen[CV()-1],0) + 1 +  NVL(len[CV()-1],0)
END
END,
grp[i] = NVL(grp[CV()-1],0) + CASE WHEN prevlen[CV()+1] < prevlen[CV()] THEN 1 ELSE 0 END
)
)
SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) AS "Names" , LENGTH(listagg(ename,',') WITHIN GROUP (ORDER BY  ename)) AS "Length"
FROM m
GROUP BY grp;

```

#7: Oracle 12c Solution – Using MATCH_RECOGNIZE clause

```SELECT  LISTAGG(name,',') WITHIN GROUP(ORDER BY name) "Names",
LENGTH(LISTAGG(name,',') WITHIN GROUP(ORDER BY name)) "Length"
FROM  EMP
MATCH_RECOGNIZE
(
ORDER BY ENAME
MEASURES
MATCH_NUMBER() rn,
UP.ENAME name
ALL ROWS PER MATCH
PATTERN (UP+)
DEFINE
UP AS SUM(LENGTH(UP.ENAME || ',')) <= 24
)
GROUP BY RN
ORDER BY RN

```

### 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 #10: Fibonacci

With a single SELECT statement calculate first 20 Fibonacci numbers without using Binet’s formula.

Expected Result:

```   N     Fib(n)
---- ----------
1          1
2          1
3          2
4          3
5          5
6          8
7         13
8         21
9         34
10         55
11         89
12        144
13        233
14        377
15        610
16        987
17       1597
18       2584
19       4181
20       6765
```

## Solutions:

#1. Oracle 10g solution (using MODEL clause):

```SELECT n, f AS "Fib(n)"
FROM dual
MODEL
DIMENSION BY (0 d)
MEASURES (0 n, 0 f)
RULES ITERATE(20) (
f[iteration_number]=DECODE(iteration_number, 0,1, 1,1,
f[iteration_number-2]+f[iteration_number-1]),
n[iteration_number]=iteration_number+1
);

N     Fib(n)
--- ----------
1          1
2          1
3          2
4          3
5          5
6          8
7         13
8         21
9         34
10         55
11         89
12        144
13        233
14        377
15        610
16        987
17       1597
18       2584
19       4181
20       6765```

#2. Oracle 11.2g solution (using Recursive WITH clause):

WITH x(n, f1, f2) AS (
SELECT 1, 1, 1
FROM dual
UNION ALL
SELECT n+1, f2, f1+f2
FROM x
WHERE n<20
)
SELECT n, f1 AS “Fib(n)”
FROM x

#3. Oracle 12c solution (using WITH for PL/SQL function):

```WITH
FUNCTION fib(n INTEGER) RETURN NUMBER DETERMINISTIC
AS
BEGIN
RETURN CASE WHEN n IN (1,2) THEN 1
ELSE fib(n-2)+fib(n-1)
END;
END;
SELECT LEVEL n, fib(LEVEL) AS "Fib(n)"
FROM dual
CONNECT BY LEVEL<=20```

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

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

## Two ways to build a salary range report without using CASE function

Interview Question: Produce a salary range report with a single SELECT statement. Decode function is allowed, CASE function – is not.

Level: Intermediate

Expected Result:

```RANGE                             Employees
-------------------------------- ----------
0-999                                     2
1000-2999                                 9
3000-5999                                 3
```

Strategy #1:

```SELECT COALESCE(DECODE(LEAST(sal, 999), sal, '0-999'),
DECODE(LEAST(sal, 2999), GREATEST(sal, 1000), '1000-2999'),
DECODE(LEAST(sal, 9999), GREATEST(sal, 3000), '3000-5999')
) AS range,
COUNT(*) "Employees"
FROM emp
GROUP BY COALESCE(DECODE(LEAST(sal, 999), sal, '0-999'),
DECODE(LEAST(sal, 2999), GREATEST(sal, 1000), '1000-2999'),
DECODE(LEAST(sal, 9999), GREATEST(sal, 3000), '3000-5999')
)
ORDER BY 1
```

Explanation:

In Oracle SQL terms, a mathematical condition

`a <=x <=b`

can be  interpreted as

`x BETWEEN a AND b`

however, this condition is good only for CASE function, and not for DECODE. The trick is to use another interpretation:

`LEAST(b,x)=GREATEST(x,a)`

– that can be used in DECODE.

CASE-based Solution:

```SELECT CASE WHEN sal<=999 THEN '0-999'
WHEN sal BETWEEN 1000 AND 2999 THEN '1000-2999'
WHEN sal BETWEEN 3000 AND 5999 THEN '3000-5999'
END AS range,
COUNT(*) "Employees"
FROM emp
GROUP BY CASE WHEN sal<=999 THEN '0-999'
WHEN sal BETWEEN 1000 AND 2999 THEN '1000-2999'
WHEN sal BETWEEN 3000 AND 5999 THEN '3000-5999'
END
ORDER BY 1
```

Strategy #2:

```WITH x AS (
SELECT DECODE(1, (SELECT COUNT(*) FROM dual WHERE emp.sal<=999), '0-999',
(SELECT COUNT(*) FROM dual WHERE emp.sal BETWEEN 1000 AND 2999), '1000-2999',
(SELECT COUNT(*) FROM dual WHERE emp.sal BETWEEN 3000 AND 5999), '3000-5999'
) AS range
FROM emp
)
SELECT range, COUNT(*) AS "Employees"
FROM x
GROUP BY range
ORDER BY 1
```

Explanation:
This query demonstrates how to mimic CASE function using DECODE and in-line scalar subquery from dual.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

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

## How to split numeric and text values into separate columns

Puzzle: There is a table T with a single column C VARCHAR2(20) that contains random values. Some of the values are numeric. Write a single SELECT statement that outputs 2 columns: NUM and TEXT with numeric and non-numeric values correspondingly.

To mimic the T table, we will create a view:

```CREATE OR REPLACE VIEW T
AS
WITH x AS (
SELECT CASE WHEN MOD(level,2)=0 THEN dbms_random.string('x',3)
ELSE TO_CHAR(TRUNC(dbms_random.VALUE(-999,999)))
END rnd,
ROW_NUMBER()OVER(ORDER BY dbms_random.value) rk
FROM dual
CONNECT BY ROWNUM<=40
)
SELECT CAST(rnd AS VARCHAR2(4)) C
FROM x
WHERE rk<=10;
```

Expected Result:
Due to the random nature of the values in T view, actual results will be different every time you run a query; however, all the results will look somewhat like this:

```NUM  TEXT
---- ----
-146 4R9
-362 78R
-762 ICY
236  U3W
VIK
Y21
```

Solution:

```WITH x AS (
SELECT c, CASE WHEN REGEXP_LIKE(c,'^-?[[:digit:]]+\$') THEN 1 ELSE 0 END is_int,
RANK()OVER(PARTITION BY CASE WHEN REGEXP_LIKE(c,'^-?[[:digit:]]+\$') THEN 1 ELSE 0 END ORDER BY c) rk
FROM t
)
SELECT MAX(DECODE(is_int, 1, c)) NUM,
MAX(DECODE(is_int, 0, c)) TEXT
FROM x
GROUP BY rk
ORDER BY rk
```

Explanation:

The above solution uses regular expression for identifying numeric integer values (positive and negative):

```^-?[[:digit:]]+\$
```

The way to break down the results into two columns was demonstrated in my previous post: Generate a department/employee roll report

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

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

## Round 1 Playoff Schedule

N teams (N between 1 and 32) just finished the season and are all qualified for the playoff. If the number of teams were 2, 4, 8, 16, or 32 (powers of 2), the playoff schedule would be trivial: 1st team plays vs last team, 2nd – vs 2nd from the last, etc. However, there is no guarantee that the number of teams would be a power of 2. The challenge is to write a single SELECT statement that accepts the number of teams as a parameter and generates the round 1 pairings.

There should be 1, 2, 4, 6, or 16 teams (power of 2) in the 2nd round.

## Solution

```WITH x AS (
SELECT &teams p
FROM dual
), y AS (
SELECT ROWNUM home, POWER(2,CEIL(LOG(2,p)))-ROWNUM+1 away,
POWER(2,CEIL(LOG(2,p))) maxp
FROM dual, x
CONNECT BY LEVEL<=POWER(2,CEIL(LOG(2,p))-1)
)
SELECT CASE WHEN away<=p AND p>1 THEN ROWNUM+p-maxp END AS "Game #",
CASE WHEN away>p AND p>1 THEN 'Team-' || home || ' advances to Round 2'
WHEN p=1 THEN 'Team-1 is a Champion!'
ELSE 'Team-' || home || ' vs Team-' || away
END AS "Playoff Round 1 Pairings"
FROM y, x
ORDER BY 1 NULLS LAST, home
```

## Explanation

The trick here was to figure out which teams should be playing and which simply advance to the Round 2. Suppose that we have a power of 2 number of teams. Then top 1st team plays against the bottom 1st, top 2ng vs bottom 2nd, etc. If there are N teams, then we will have N/2 games. This is the simplest case. What if we have 6 teams? We should add 2 fake teams to “round up” the number of teams to the nearest power of 2 that is greater or equal to N. For 6 teams, we round up to 8. Those 2 fake teams should be paired against top 2 teams, and this gives us an answer which teams should advance to the Round 2 without playing. General rule, if we have to add K “faked teams” to “round up” to the nearest power of 2 number, this means that top K teams advance to the next round without playing.

We used CEIL, LOG, and POWER functions to get the next power of 2 for any whole N:

```POWER(2,CEIL(LOG(2,p)))
```

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.

## Puzzle of the week #3 Solutions

Puzzle of the week #3Calendar Summary Report:

Write a single SELECT statement that outputs number of Sundays, Mondays, Tuesdays, etc in each month of the current year.

The output should look like this:

```MONTH  SUN  MON  TUE  WED  THU  FRI  SAT
----- ---- ---- ---- ---- ---- ---- ----
JAN      5    4    4    4    4    5    5
FEB      4    5    4    4    4    4    4
MAR      4    4    5    5    5    4    4
APR      4    4    4    4    4    5    5
MAY      5    5    5    4    4    4    4
JUN      4    4    4    5    5    4    4
JUL      5    4    4    4    4    5    5
AUG      4    5    5    5    4    4    4
SEP      4    4    4    4    5    5    4
OCT      5    5    4    4    4    4    5
NOV      4    4    5    5    4    4    4
DEC      4    4    4    4    5    5    5
```

We suggest you to go over the post that explains how to generate various date ranges before checking the solutions below.

Solution #1: Using PIVOT simulation

```WITH days AS (
SELECT TRUNC(SYSDATE,'YEAR')+ROWNUM-1 d
FROM dual
CONNECT BY TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'YYYY')=TO_CHAR(SYSDATE,'YYYY')
)
SELECT TO_CHAR(d,'MON') Month,
SUM(CASE WHEN TO_CHAR(d,'DY')='SUN' THEN 1 END) SUN,
SUM(CASE WHEN TO_CHAR(d,'DY')='MON' THEN 1 END) MON,
SUM(CASE WHEN TO_CHAR(d,'DY')='TUE' THEN 1 END) TUE,
SUM(CASE WHEN TO_CHAR(d,'DY')='WED' THEN 1 END) WED,
SUM(CASE WHEN TO_CHAR(d,'DY')='THU' THEN 1 END) THU,
SUM(CASE WHEN TO_CHAR(d,'DY')='FRI' THEN 1 END) FRI,
SUM(CASE WHEN TO_CHAR(d,'DY')='SAT' THEN 1 END) SAT
FROM days
GROUP BY TO_CHAR(d,'MON'), TO_CHAR(d,'MM')
ORDER BY TO_CHAR(d,'MM');
```

Solution #2: Using PIVOT

```SELECT month, mon, sun, mon, tue, wed, thu, fri, sat
FROM
(
SELECT TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'MON') month,
TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'DY') dy,
TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'MM') mm
FROM dual
CONNECT BY TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'YYYY')=TO_CHAR(SYSDATE,'YYYY')
)
PIVOT
(
COUNT(dy)
FOR dy IN ('SUN' sun, 'MON' mon, 'TUE' tue, 'WED' wed, 'THU' thu, 'FRI' fri, 'SAT' sat)
)
ORDER BY mm;
```

Solution #3: Using PIVOT simulation and Recursive WITH

```WITH days(d) AS
(
SELECT TRUNC(SYSDATE,'YEAR') d
FROM dual
UNION ALL
SELECT d+1
FROM days
WHERE TO_CHAR(d+1,'YYYY')=TO_CHAR(SYSDATE,'YYYY')
)
SELECT TO_CHAR(d,'MON') Month,
SUM(CASE WHEN TO_CHAR(d,'DY')='SUN' THEN 1 END) SUN,
SUM(CASE WHEN TO_CHAR(d,'DY')='MON' THEN 1 END) MON,
SUM(CASE WHEN TO_CHAR(d,'DY')='TUE' THEN 1 END) TUE,
SUM(CASE WHEN TO_CHAR(d,'DY')='WED' THEN 1 END) WED,
SUM(CASE WHEN TO_CHAR(d,'DY')='THU' THEN 1 END) THU,
SUM(CASE WHEN TO_CHAR(d,'DY')='FRI' THEN 1 END) FRI,
SUM(CASE WHEN TO_CHAR(d,'DY')='SAT' THEN 1 END) SAT
FROM days
GROUP BY TO_CHAR(d,'MON'), TO_CHAR(d,'MM')
ORDER BY TO_CHAR(d,'MM');
```

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.