ORACLE table top left not null value

Question

How can I select top left not null value from the table/subquery?

expl:

colfkey     col1 | col2 | col3 | col4  
xxxxxxx     null   null   '1'    null  
xxxxxxx     null    '2'   null   null   

I need only the '1' value

Answer 1

Do you mean something like this:

SELECT colfkey, COALESCE( col1, col2, col3, col4 ) AS value
FROM   table_name
WHERE  ROWNUM = 1
AND    COALESCE( col1, col2, col3, col4 ) IS NOT NULL;

SQL FIDDLE

If you don't then please explain more about what your requirement is.

Answer 2

My answer assumes that you want to order the results by one the col values, otherwise, there's no way of knowing what is the order of the results :

SELECT * FROM
 (SELECT CASE WHEN "col1" IS NOT NULL THEN "col1"
   WHEN "col2" IS NOT NULL THEN "col2"
   WHEN "col3" IS NOT NULL THEN "col3"
   WHEN "col4" IS NOT NULL THEN "col4" END AS TOP_VALUE
   FROM expl
 ORDER BY 1 ASC) a
WHERE rownum = 1

sqlfiddle demo