On Jul 7, 2007, at 2:15 PM, Tom Lane wrote:
Perry Smith <pedz@xxxxxxxxxxxxxxxx> 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;
