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

# 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
• You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points. This is the LAST PUZZLE of this contest.
• The scoring rules can be found here.
• Solutions must be submitted as comments to this blog post.
• Use <pre>or <code> html tags around your SQL code for better formatting and to avoid losing parts of your SQL.

Expected Result (for a phone number 1-800-123-4357):

PHONE
1-800-123-GEJP
1-800-123-GEJQ
1-800-123-GEJR
1-800-123-GEJS
1-800-123-GEKP
1-800-123-GEKQ
1-800-123-GEKR
1-800-123-GEKS
1-800-123-GELP
1-800-123-GELQ
1-800-123-GELR
1-800-123-GELS
1-800-123-HEJP
1-800-123-HEJQ
1-800-123-HEJR
1-800-123-HEJS
1-800-123-HEKP
1-800-123-HEKQ
1-800-123-HEKR
1-800-123-HEKS
1-800-123-HELP
1-800-123-HELQ
1-800-123-HELR
1-800-123-HELS
1-800-123-IDJP
1-800-123-IDJQ
1-800-123-IDJR
1-800-123-IDJS
1-800-123-IDKP
1-800-123-IDKQ
1-800-123-IDKR
1-800-123-IDKS
1-800-123-IDLP
1-800-123-IDLQ
1-800-123-IDLR
1-800-123-IDLS
1-800-123-IFJP
1-800-123-IFJQ
1-800-123-IFJR
1-800-123-IFJS
1-800-123-IFKP
1-800-123-IFKQ
1-800-123-IFKR
1-800-123-IFKS
1-800-123-IFLP
1-800-123-IFLQ
1-800-123-IFLR
1-800-123-IFLS

Apparently, the purpose of this exercise is to pick a string that sounds the best, so in this particular case we would pick: 1-800-123-HELP.

A correct answer (and workarounds!) will be published here in about a week.

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.

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

## Duplication SQL Puzzle

Each row in a table should be duplicated certain number of times:
1st row – N times (where N = total number of rows in the table)
2nd and 3rd rows – (N-1) times
4th, 5th, 6th – (N-2) times
7,8,9,10 – (N-3) times
etc

Let’s assume that we have a table t with the content:

LETTER
A
B
C
D
E
F

So the record A needs to be repeated 6 times, B and C – 5 times, D, E, and F  – 4 times.

This can be summarized the following way:

LETTER Rule REPS
A N reps 6
B N-1 reps 5
C N-1 reps 5
D N-2 reps 4
E N-2 reps 4
F N-2 reps 4

Expected Result (28 rows):

LETTER REPS
A 6
A 6
A 6
A 6
A 6
A 6
B 5
B 5
B 5
B 5
B 5
C 5
C 5
C 5
C 5
C 5
D 4
D 4
D 4
D 4
E 4
E 4
E 4
E 4
F 4
F 4
F 4
F 4

The video below demonstrates a couple of techniques aiming to solve this puzzle:

The source code can be also seen here.

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

# 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 Oracle SQL Puzzle of the Week #1

This puzzle is sometimes offered at non-SQL technical interviews with companies like Google or Amazon:

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

For example, the longest sub-string with no repeating characters in the word anaconda is acond; and in the word: stereotype – it is reotyp.

We move the bar higher and will solve this quite challenging problem in Oracle SQL:

• Remember to use only a single SELECT statement.
• The character case should be ignored (use or convert the value to a lower case)
• You have about 1 week to solve the puzzle and submit your solution(s) but whoever does it sooner will earn more points.
• The scoring rules can be found here.

If this puzzle is a bit tricky for you, start with a simpler one:

For a given text string, list all possible sub-strings.

If you are ready to give up, please don’t – try to come up with a PL/SQL solution (at very least). It could be a function that takes a VARCHAR2 argument and returns a VARCHAR2 result value.

A correct answer (and workarounds!) will be published here in a week.

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

## How to Simulate SIGN Function

### Puzzle of the day:

How to simulate the SIGN function in Oracle SQL by only using CEIL, FLOOR, and ABS Oracle SQL functions along with arithmetic operators? No PL/SQL.

### Solution:

```SIGN(x)=CEIL(x/(1+ABS(x)))+FLOOR(x/(1+ABS(x)))
```

In SQL, we can demonstrate it as follows:

```WITH r AS (
SELECT dbms_random.VALUE(-999,999) rnd
FROM dual
CONNECT BY LEVEL<=10
UNION ALL
SELECT 0
FROM dual
)
SELECT rnd, SIGN(rnd), CEIL(rnd/(1+ABS(rnd)))+FLOOR(rnd/(1+ABS(rnd))) "MySign"
FROM r
```

Result:

```       RND  SIGN(RND)     MySign
---------- ---------- ----------
-519.606         -1         -1
-657.62692         -1         -1
414.625079          1          1
736.175183          1          1
268.689074          1          1
-647.12649         -1         -1
338.192233          1          1
784.780876          1          1
-529.69184         -1         -1
-596.56803         -1         -1
0          0          0
```

As you can see, “MySign” column perfectly matches SIGN column.

### Comment:

WITH clause is needed to generate 10 random values in the range of -999 .. +999. “0” value is added to demonstrate a special case as it is unlikely that zero will be randomly generated.

### 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 #21:

Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:

• Use Single SELECT statement only
• Use mgr column to identify employee’s immediate manager
• The query should work in Oracle 11g.
• A preferred solution should use only a single instance of emp table.

### Expected Result:

```NAME1      NAME2      NAME3
---------- ---------- ------
SMITH      FORD       JONES
ALLEN      BLAKE      KING
WARD       BLAKE      KING
JONES      KING
MARTIN     BLAKE      KING
BLAKE      KING
CLARK      KING
SCOTT      JONES      KING
KING
TURNER     BLAKE      KING
JAMES      BLAKE      KING
FORD       JONES      KING
MILLER     CLARK      KING```

### Solutions:

#1. Using connect_by_root, sys_connect_by_path, and regexp_substr functions

```col name1 for a10
col name2 for a10
col name3 for a10
WITH x AS(
SELECT CONNECT_BY_ROOT(ename) name,
SYS_CONNECT_BY_PATH(ename, ',') path,
CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT name, REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 2) name2,
REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 3) name3
FROM x
GROUP BY name, empno
ORDER BY empno;```

#2. Using CONNECT BY twice

```WITH x AS (
SELECT ename, PRIOR ename mname, empno, mgr
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT ename name1, mname name2, MAX(PRIOR mname) name3
FROM x
WHERE LEVEL<=2
CONNECT BY PRIOR empno=mgr
GROUP BY ename, mname, empno
ORDER BY empno```

#3. Using CONNECT BY and Self Outer Join

```WITH x AS (
SELECT ename, PRIOR ename mname, PRIOR mgr AS mgr, empno
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT x.ename name1, x.mname name2, e.ename name3
FROM x LEFT JOIN emp e ON x.mgr=e.empno
ORDER BY x.empno```

#4. Using 2 Self Outer Joins

```SELECT a.ename name1, b.ename name2, c.ename name3
FROM emp a LEFT JOIN emp b ON a.mgr=b.empno
LEFT JOIN emp c ON b.mgr=c.empno
ORDER BY a.empno```

#5. Using CONNECT BY and PIVOT

```SELECT name1, name2, name3
FROM (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
PIVOT(
MAX(ename)
FOR lvl IN (1 AS name1, 2 AS name2, 3 AS name3)
)
ORDER BY empno;```

#6. PIVOT Simulation

```WITH x AS (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT MAX(DECODE(lvl, 1, ename)) name1,
MAX(DECODE(lvl, 2, ename)) name2,
MAX(DECODE(lvl, 3, ename)) name3
FROM x
GROUP BY empno
ORDER BY empno;```

#7. Using CONNECT BY and no WITH/Subqueries (Credit to Krishna Jamal)

```SELECT ename Name1, PRIOR ename Name2,
DECODE(LEVEL,
3, CONNECT_BY_ROOT(ename),
4, TRIM(BOTH ' ' FROM
REPLACE(
REPLACE(SYS_CONNECT_BY_PATH(PRIOR ename, ' '), PRIOR ename),
CONNECT_BY_ROOT(ename)))
) Name3
FROM emp
CONNECT BY PRIOR empno = mgr
ORDER BY empno;```

#8. A composition of Methods 1 and 7:

```SELECT ename Name1, PRIOR ename Name2,
CASE WHEN LEVEL IN (3,4)
THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ename, ','),'[^,]+',1,LEVEL-2)
END AS Name3
FROM emp
CONNECT BY PRIOR empno = mgr
ORDER BY empno;```

#9. Using NTH_VALUE Analytic function (Oracle 11.2 and up):

```WITH x AS (
SELECT CONNECT_BY_ROOT(ename) n1, CONNECT_BY_ROOT(empno) empno,
NTH_VALUE(ename, 2) OVER(PARTITION BY CONNECT_BY_ROOT(ename) ORDER BY LEVEL) n2,
NTH_VALUE(ename, 3) OVER(PARTITION BY CONNECT_BY_ROOT(ename) ORDER BY LEVEL) n3
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT n1 name1, MAX(n2) name2, MAX(n3) name3
FROM x
GROUP BY n1, empno
ORDER BY empno```

### 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 #21:

Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:

• Use Single SELECT statement only
• Use mgr column to identify employee’s immediate manager
• The query should work in Oracle 11g.
• A preferred solution should use only a single instance of emp table.

### Expected Result:

```NAME1      NAME2      NAME3
---------- ---------- ------
SMITH      FORD       JONES
ALLEN      BLAKE      KING
WARD       BLAKE      KING
JONES      KING
MARTIN     BLAKE      KING
BLAKE      KING
CLARK      KING
SCOTT      JONES      KING
KING
TURNER     BLAKE      KING
JAMES      BLAKE      KING
FORD       JONES      KING
MILLER     CLARK      KING```

A correct answer (and workarounds!) will be published here in a week.

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