hallo ghassan,
great !!
it look likes this what exactly what I was looking for.
it actually is simple, but you have to see the trick.
(and I didn't)
thank you
matthias
ghassan salem wrote:
> 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 Fri Dec 19 2003 - 03:07:45 CST
