Re: CASE in ORDER BY clause

Perry Smith wrote:
> I see.  So, in effect he has:
> 
>     ORDER BY NULL DESC, start_date ASC;
> 
> or
> 
>   ORDER BY start_date DESC, NULL ASC;

Not exactly.  He has (first sort clause) *and* (second sort clause), not "or".

Both sort clauses operate at all rows.

You are sort of correct in that for any given row, its position in the SELECT 
order will be determined by exactly one of

(start_date, NULL) for future dates
or
(NULL, start_date) for past dates
.

It is incorrect to see that as two separate ORDER BY clauses.

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?

-- 
Lew