Re: CASE in ORDER BY clause

On Sat, Jul 07, 2007 at 01:49:09PM -0500, Perry Smith wrote:
> >># 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;
> >>
> I am very novice, but that looks odd to me. I would have expected
> the asc or desc keywords need to go inside the case (before the
> end). Otherwise you have either:

The keyword asc/desc applies to an expression, the result is not an
expression, hence you cannot put the asc/desc inside a case.

> ... order by start_date desc, asc;
> or
> ... order by desc, start_date asc;

Almost, it's actually:
... order by start_date desc, null asc;
or
... order by null desc, start_date asc;

Ordering by a constant has no effect, which is why it works.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.