Home » SQL & PL/SQL » SQL & PL/SQL » forum thread like query using connect by... (9i, 10g, 11g)
forum thread like query using connect by... [message #375790] Sun, 14 December 2008 13:15 Go to next message
kang
Messages: 89
Registered: November 2007
Member
Let's say there is a table for message board,
and I need a query for

first, level : descending on msg_dt
second : ascending on msg_dt

any ideas?

create table t
( key        varchar2(10) ,
  parent_key varchar2(10) ,
  name       varchar2(10), 
  msg_dt date
);

insert into t values ('1', null, 'NLS',to_date('20080101','yyyymmdd'));
insert into t values ('2', '1', 'DATA',to_date('20080102','yyyymmdd'));
insert into t values ('3', '1', 'DEMO',to_date('20080103','yyyymmdd'));

insert into t values ('4', null, 'SERVER',to_date('20080104','yyyymmdd'));
insert into t values ('5', '4', 'BIN',to_date('20080105','yyyymmdd'));
insert into t values ('6', '4', 'CONFIG',to_date('20080106','yyyymmdd'));
insert into t values ('7', '4', 'LOG',to_date('20080107','yyyymmdd'));


select
  lpad(' ', level-1) || key
    as key_indented
from
  t
start with
  parent_key is null
connect by
  parent_key = prior key
ORDER SIBLINGS BY
  KEY ASC
;
this query above gives
1
 2
 3
4
 5
 6
 7


but as I said I want
4
 5
 6
 7
1
 2
 3


ie : first, level 1(1,4) is descending(recent first)
second others, ascending(oldest first)

Thanks.
Re: forum thread like query using connect by... [message #375797 is a reply to message #375790] Sun, 14 December 2008 14:33 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT	key_indented
  2  FROM    (select  lpad (' ', level - 1) || key   as key_indented,
  3  		      CONNECT_BY_ROOT KEY	     AS KEY_ROOT,
  4  		      SYS_CONNECT_BY_PATH (KEY, ' ') AS KEY_PATH
  5  	      from    t
  6  	      start   with parent_key is null
  7  	      connect by parent_key = prior key)
  8  ORDER   BY KEY_ROOT DESC, KEY_PATH ASC
  9  /

KEY_INDENTED
--------------------------------------------------------------------------------
4
 5
 6
 7
1
 2
 3

7 rows selected.

SCOTT@orcl_11g> 

Previous Topic: how to emulate "order siblings by" in 8i?
Next Topic: Extension of UTL_File
Goto Forum:
  


Current Time: Fri Dec 09 17:16:13 CST 2016

Total time taken to generate the page: 0.13391 seconds