Home » SQL & PL/SQL » SQL & PL/SQL » Order by (9.2.0.8)
Order by [message #288689] Tue, 18 December 2007 05:55 Go to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Hi guys

Need some help for doing an order by.
Intrested in if this can be solved by using analytics functionallity.

Any help much appreciated

Here's an example

hpl@UTV9> CREATE TABLE dt (LNR INTEGER,START_DATE DATE ,END_DATE DATE);

Tabellen är skapad.

hpl@UTV9> INSERT INTO DT (
  2     LNR, START_DATE, END_DATE) 
  3  VALUES (1 ,'2005-12-05' ,NULL );

1 rad är skapad.

hpl@UTV9> INSERT INTO DT (
  2     LNR, START_DATE, END_DATE) 
  3  VALUES (2 ,'2005-12-07' ,'2005-12-08' );

1 rad är skapad.

hpl@UTV9> INSERT INTO DT (
  2     LNR, START_DATE, END_DATE) 
  3  VALUES (3 ,NULL ,'2005-12-06' );

1 rad är skapad.

hpl@UTV9> commit;

COMMIT är utfört.

hpl@UTV9> SELECT   LNR, START_DATE, END_DATE
  2      FROM DT
  3  ORDER BY START_DATE ASC, END_DATE ASC;

       LNR START_DATE END_DATE
---------- ---------- ----------
         1 2005-12-05
         2 2005-12-07 2005-12-08
         3            2005-12-06


The question is: I need to get the result to look like this

       LNR START_DATE END_DATE
---------- ---------- ----------
         1 2005-12-05
         3            2005-12-06
         2 2005-12-07 2005-12-08



Re: Order by [message #288692 is a reply to message #288689] Tue, 18 December 2007 06:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
nvl(start_date, end_date)
Re: Order by [message #288695 is a reply to message #288689] Tue, 18 December 2007 06:13 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

didn't try with ananlytical func.

with data as 
  (
    select 1 lnr,to_date('2005-12-05','yyyy-mm-dd') start_date,NULL end_date from dual union all
    select 2 ,to_date('2005-12-07','yyyy-mm-dd') ,to_date('2005-12-08','yyyy-mm-dd') from dual union all
    select 3 ,NULL ,to_date('2005-12-06','yyyy-mm-dd') from dual
  )
select * from data order by coalesce(start_date,end_date)


regards,
Re: Order by [message #288700 is a reply to message #288695] Tue, 18 December 2007 06:39 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Damn I suck Smile

Thank you
Re: Order by [message #288709 is a reply to message #288700] Tue, 18 December 2007 07:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
hehe, kind of the d'oh! reaction I expected.
The issue however is all too familiar: once you got a direction for a solution into your head, it normally takes someone else to point you to the blatant obvious other solution Smile

Been there, done that. And I bet everyone with enough experience saying it never happened to him/her is, let's call it "truth-challenged"
Re: Order by [message #288711 is a reply to message #288709] Tue, 18 December 2007 07:11 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Thank you Frank

I really felt like good old Homer Smile
Previous Topic: PFILE and SPFILE
Next Topic: renaming column at run time
Goto Forum:
  


Current Time: Mon Dec 05 03:06:26 CST 2016

Total time taken to generate the page: 0.14088 seconds