select
substr(min(decode(num1, null, null, to_char(priority)||to_char(num1))), 2) num1,
substr(min(decode(num2, null, null, to_char(priority)||to_char(num2))), 2) num2,
substr(min(decode(num3, null, null, to_char(priority)||to_char(num3))), 2) num3,
substr(min(decode(num4, null, null, to_char(priority)||to_char(num4))), 2) num4
from test
/
rogel_at_web.de (matthias) wrote in message news:<a22c708a.0312180705.635273cb_at_posting.google.com>...
> this might be a simple question, but I can't get it
> with analytics:
>
> SQL> desc test
> Name Null? Typ
> ----------------------------------------- -------- ----------------------------
> PRIORITY NUMBER(38)
> NUM1 NUMBER
> NUM2 NUMBER
> NUM3 NUMBER
> NUM4 NUMBER
>
> SQL> select * from test order by priority asc;
>
> PRIORITY NUM1 NUM2 NUM3 NUM4
> ---------- ---------- ---------- ---------- ----------
> 1 2
> 2 2,1 4
> 3 4,3 5
>
>
>
> What I want to get within a query is
> - the first not null NUM1 (or null if not present)
> - the first not null NUM2 (or null if not present)
> - the first not null NUM3 (or null if not present)
> - the first not null NUM4 (or null if not present)
>
>
> so the result should look like
>
> NUM1 NUM2 NUM3 NUM4
> 2 NULL 4 5
>
> how to achieve this with analytic functions ?
>
> thank you
>
> matthias
Received on Thu Dec 18 2003 - 16:06:00 CST
