Re: CASE in ORDER BY clause

On Jul 7, 2007, at 2:15 PM, Tom Lane wrote:

> Perry Smith <pedz@easesoftware.com> writes:
>>> On Fri, Jul 06, 2007 at 08:02:54PM +0400, Viatcheslav Kalinin 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).
>
> No, the syntax is
>     ORDER BY <expr> ASC, ...
> or
>     ORDER BY <expr> DESC, ...
>
> and in this case the expression is a CASE construct.  Viatcheslav
> is omitting an ELSE clause which means there is an implied ELSE NULL
> in each of the CASEs, and he's relying on some rules he didn't mention
> about NULLs sorting before or after all non-null values, plus the
> normal behavior of two-column sorts.
>
> The bottom line is that ASC and DESC can only appear at the top
> level of
> the ORDER BY syntax.  I have no idea what it would mean to put them
> somewhere else --- you'd have to invent semantics like mad to assign a
> meaning to that at all.

I see.  So, in effect he has:

     ORDER BY NULL DESC, start_date ASC;

or

   ORDER BY start_date DESC, NULL ASC;