Re: CASE in ORDER BY clause

"Louis-David Mitterrand" <vindex+lists-pgsql-general(at)apartia(dot)org> writes:

> However if I try:
>
> select start_date from show_date order by 'start_date desc';
>
> I get a:
>
> ERROR: non-integer constant in ORDER BY

The quotes mean you are asking to sort by the string "start_date desc" which
is an error because sorting by a constant value is pointless.

Moreover, "asc" and "desc" are part of the order by and have to go after the
expression. Look more carefully at the original suggestion:

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;

This is two sorting expressions, one of which will be null for all rows but
which one is null will vary from row to row.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com