Re: CASE in ORDER BY clause

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.

Uwe

--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416