Re: Hierarchical query

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 21 Mar 2005 20:27:11 +0100
Message-ID: <423f1fbe$0$1570$636a15ce@news.free.fr>

"RT" <robbyt_at_unocal.com> a écrit dans le message de news:1111384975.161518.161250_at_f14g2000cwb.googlegroups.com...
| I got a table with the following format:
|
| insert into tab1 (1,'E1');
| insert into tab1 (2,'E2');
| insert into tab1 (3,'E3');
| insert into tab1 (4,'Salary');
| insert into tab1 (5,'E4');
| insert into tab1 (6,'E5');
| insert into tab1 (7,'E6');
| insert into tab1 (8,'E7');
| insert into tab1 (9,'Benefit');
|
| and I want to ouput the following:
|
| E1, Salary
| E2, Salary
| E3, Salary
| E4, Benefit
| E5, Benefit
| E6, Benefit
| E7, Benefit
|
| Can anybody please help me with the query.
| Many thanks in advance.
|

SQL> create table tab1 (col1 number, col2 varchar2(10));
SQL> insert into tab1 values (1,'E1');
SQL> insert into tab1 values (2,'E2');
SQL> insert into tab1 values (3,'E3');
SQL> insert into tab1 values (4,'Salary');
SQL> insert into tab1 values (5,'E4');
SQL> insert into tab1 values (6,'E5');
SQL> insert into tab1 values (7,'E6');
SQL> insert into tab1 values (8,'E7');
SQL> insert into tab1 values (9,'Benefit');
SQL> commit;
SQL> select * from tab1;

      COL1 COL2
---------- ----------
         1 E1
         2 E2
         3 E3
         4 Salary
         5 E4
         6 E5
         7 E6
         8 E7
         9 Benefit

9 rows selected.

Old style

---

SQL> select b.col2, a.col2
  2 from tab1 a, tab1 b

  3  where a.col2 not like 'E_'
  4    and b.col2 like 'E_'
  5    and b.col1 < a.col1
  6    and not exists (select 1 from tab1 c
  7           where c.col1>b.col1 and c.col1<a.col1 and c.col2 not like 'E_')

  8 order by 1
  9 /

COL2 COL2
---------- ----------

E1         Salary
E2         Salary
E3         Salary
E4         Benefit
E5         Benefit
E6         Benefit
E7         Benefit

7 rows selected.

With analytic functions

---

(faster but harder to read)

SQL> select col2, substr(ocol2,5) col2
  2 from ( select col1, col2,

  3       min(rn) over (order by col1 desc) ocol2
  4       from ( select col1, col2,
  5       case when col2 not like 'E_' then to_char(rn,'999')||col2
  6            else null end rn
  7       from ( select col1, col2,
  8       row_number() over (order by col1) rn
  9       from tab1 )
 10     )  )

 11 where col2 like 'E_'
 12 order by col1
 13 /

COL2 COL2
---------- ----------

E1         Salary
E2         Salary
E3         Salary
E4         Benefit
E5         Benefit
E6         Benefit
E7         Benefit

7 rows selected.

Regards
Michel Cadot Received on Mon Mar 21 2005 - 13:27:11 CST