Re: CASE in ORDER BY clause

Perry Smith <pedz(at)easesoftware(dot)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.

regards, tom lane