2018 Oracle SQL Puzzle of the Week #10

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)
  • Try to come up with 2-3 different solutions.
  • 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.
  • 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:

DEPTNO MAX_SAL
10 5000
20 3000
30 2850

A correct answer (and workarounds!) will be published here in about 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.

 

Advertisements

8 Solutions to 2018 Oracle SQL Puzzle of the Week #9

Recent employment Puzzle

For each location, show 2 most recently hired employees

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

Expected Result:

LOC ENAME1 HIREDATE1 ENAME2 HIREDATE2
NEW YORK MILLER 23-JAN-82 KING 17-NOV-81
CHICAGO JAMES 03-DEC-81 MARTIN 28-SEP-81
DALLAS ADAMS 23-MAY-87 SCOTT 19-APR-87

Solutions:

Solution #1. Using Self-Join and MAX functions

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

Solution #2. Using LISTAGG and REGEXP_SUBSTR functions

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

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

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

Solution #4. Using Pivot

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

Solution #5. Simulating Pivot with MAX and DECODE functions

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

Solution #6. Using CONNECT BY

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

Solution #7. Using LEAD and ROW_NUMBER Analytic functions

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

Solution #8. Using Model Clause:

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

You can execute the above SQL statements in Oracle Live SQL environment.
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 #9

Recent employment Puzzle

For each location, show 2 most recently hired employees

  • Use a single SELECT statement only.
  • ename1 and hiredate1 columns should correspond the latest hired employee while ename1 and hiredate1 columns – the previous one
  • Try to come up with 2-3 different solutions.
  • 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.
  • 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:

LOC ENAME1 HIREDATE1 ENAME2 HIREDATE2
NEW YORK MILLER 23-JAN-82 KING 17-NOV-81
CHICAGO JAMES 03-DEC-81 MARTIN 28-SEP-81
DALLAS ADAMS 23-MAY-87 SCOTT 19-APR-87

A correct answer (and workarounds!) will be published here in about 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.

7 Solutions to 2018 Oracle SQL Puzzle of the Week #8

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

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.
  • Try to come up with 2-3 different solutions.
  • 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.
  • 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 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

A correct answer (and workarounds!) will be published here in about 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.

9 Solutions to 2018 Oracle SQL Puzzle of the Week #7

Namesake Puzzle

Show groups of employees having the same last name.

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

Solutions:

Solution #1: Using Subquery with HAVING clause:

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

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

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

Solution #3: Using Subquery with IN operator

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

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

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

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

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

Solution #6: Mimicking COUNT analytic function with MODEL clause

(credit to Naoto Katayama)

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

Solution #7: Filtering by LEAD and LAG analytic functions

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

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

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

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

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

You can execute the above SQL statements in Oracle Live SQL environment.
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 #7

Namesake Puzzle

Show groups of employees having the same last name.

  • Use a single SELECT statement only
  • Use hr.employees table
  • 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.
  • 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:

LAST_NAME FIRST_NAME DEPARTMENT_ID EMPLOYEE_ID
Cambrault Gerald 80 148
Cambrault Nanette 80 154
Grant Douglas 50 199
Grant Kimberely 178
King Janette 80 156
King Steven 90 100
Smith Lindsey 80 159
Smith William 80 171
Taylor Jonathon 80 176
Taylor Winston 50 180

A correct answer (and workarounds!) will be published here in about 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.

7 Solutions to 2018 Oracle SQL Puzzle of the Week #6

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

 

Term Replication Sequence SQL Puzzle

SQL Puzzle:

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

Level: Advanced

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

Complexity Level: Advanced

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.