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

jason_at_cyberpine.com wrote:
> 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!

Order By sorts ascending by default. Change the line 'ORDER BY better' to 'ORDER BY better desc'.

I am not sure why you would want to apply where clauses to dual but something like this would work:-

select *
from
(
  select 'x' mycol from dual
)
where mycol = 'x' Received on Fri Mar 03 2006 - 05:17:06 CST