One option would be:
Code:SQL> SELECT * FROM sparse; NR VALUE ---------- ---------- 1 10 2 5 10 3 11 8 SQL> SELECT nr, 2 value 3 FROM ( SELECT lvl nr, 4 value , 5 lag(value, 1, 0) over (ORDER BY lvl) lg 6 FROM sparse 7 RIGHT OUTER JOIN 8 ( SELECT lvl 9 FROM ( SELECT level lvl, 10 mn 11 FROM (SELECT MIN(nr) mn, 12 MAX(nr) mx 13 FROM sparse) 14 CONNECT BY level <= mx) 15 WHERE lvl >= mn) 16 ON nr = lvl) 17 WHERE value IS NOT NULL 18 OR lg IS NOT NULL; NR VALUE ---------- ---------- 1 10 2 5 3 10 3 11 8




Reply With Quote