SQL question

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 - 09:05:09 CST