Return the better of two rows based on availablity and preference - NOOB!

From: <jason_at_cyberpine.com>
Date: 2 Mar 2006 15:18:14 -0800
Message-ID: <1141341494.757032.105130@i40g2000cwc.googlegroups.com>

Total Noob questions.

I've constructed the following two SQL just for testing. The idea being I prefer the row with value '1' in better, but want row with better = 2 if so_arg in row better='1' is blank, empty or null.

SELECT so_arg
FROM (
   select '1' better, NULL' so_arg

      FROM dual
    UNION ALL
    SELECT '2' better, 'b' so_arg
      FROM DUAL
    ORDER BY better

          )
WHERE ROWNUM = 1; This test produced the undersired result of 'NULL'. I'm looking for 'b'.

BTW, how can I do a where on fixed values of a select from dual?

SQL> select '1' so_arg from dual where so_arg = '1'; select '1' so_arg from dual where so_arg = '1'

                                   *

ERROR at line 1:
ORA-00904: "SO_ARG": invalid identifier

Thanks in advance for any help or information! Received on Thu Mar 02 2006 - 17:18:14 CST