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@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.