Search Postgresql Archives

Re: CASE in ORDER BY clause

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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;




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux