Re: CASE in ORDER BY clause

Uwe C. Schroeder wrote:
>
> On Saturday 07 July 2007, Lew wrote:
>
>> So if your RDBMS sorts NULLs after all other values, then from
>>
>>>> select start_date from show_date
>>>> order by
>>>> case when start_date > CURRENT_DATE then start_date end desc,
>>>> case when start_date <= CURRENT_DATE then start_date end asc;
>> all rows with start_date > CURRENT_DATE will appear first, in start_date
>> descending order,
>> then all rows with start_date <= CURRENT_DATE will appear, in start_date
>> ascending order.
>>
>> Is CURRENT_DATE evaluated once for the query or twice for each row?
>
> CURRENT_DATE is evaluated once per transaction. If you run in autocommit -
> mode, then the single query is wrapped in a transaction by itself.
> Either way it's never evaluated per occurrence.
>

I'm coming in late on this but you might try something like...

select ... from (
select
...
case when start_date > current date
then 1 || start_date - current_date
else 0 || current_date - start_date end "FOO"
)
order by FOO desc

Or something like that...