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