sql question - "first_not_null_value" ?

(sorry for crossposting, already posted this to comp.databases.oracle.server which might be the wrong group, haven't been in these forums since ages ...)

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 - 10:27:01 CST