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