matthias wrote:
> 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
try this
select distinct first_value (num1) over (order by case when num1 is null
then 1 else 0 end, priority), same for num2, num3, and num4
from test
Received on Thu Dec 18 2003 - 10:36:55 CST
