Postgres Pro
Downloads
Home   >   mailing lists

Thread: CASE in ORDER BY clause

CASE in ORDER BY clause

From
Louis-David Mitterrand
Date:
Hi,

I am trying the following:

    critik=# select start_date from show_date order by case when start_date > CURRENT_DATE then start_date desc else
start_dateasc end; 
    ERROR:  syntax error at or near "desc"
    LINE 1: ...se when start_date > CURRENT_DATE then start_date desc else
    ...

If I remove the "desc" and "asc" then the quey is accepted but doesn't
do what I want.

OTOH if I try:

    critik=# select start_date from show_date order by start_date case when start_date > CURRENT_DATE then desc else
ascend; 
    ERROR:  syntax error at or near "case"
    LINE 1: ...ect start_date from show_date order by start_date case when ...

How can i order ASC or DESC depending on a condition?

Thanks,

Re: CASE in ORDER BY clause

From
Viatcheslav Kalinin
Date:
Louis-David Mitterrand wrote:
> Hi,
>
> I am trying the following:
>
>     critik=# select start_date from show_date order by case when start_date > CURRENT_DATE then start_date desc else
start_dateasc end; 
>     ERROR:  syntax error at or near "desc"
>     LINE 1: ...se when start_date > CURRENT_DATE then start_date desc else
>     ...
>
> If I remove the "desc" and "asc" then the quey is accepted but doesn't
> do what I want.
>
> OTOH if I try:
>
>     critik=# select start_date from show_date order by start_date case when start_date > CURRENT_DATE then desc else
ascend; 
>     ERROR:  syntax error at or near "case"
>     LINE 1: ...ect start_date from show_date order by start_date case when ...
>
> How can i order ASC or DESC depending on a condition?
>
> Thanks,
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
Try this:

# select start_date from show_date
# order by
# case when start_date > CURRENT_DATE then start_date end desc,
#      when start_date <= CURRENT_DATE then start_date end asc;

Re: CASE in ORDER BY clause

From
Viatcheslav Kalinin
Date:
Louis-David Mitterrand wrote:
> Hi,
>
> I am trying the following:
>
>     critik=# select start_date from show_date order by case when start_date > CURRENT_DATE then start_date desc else
start_dateasc end; 
>     ERROR:  syntax error at or near "desc"
>     LINE 1: ...se when start_date > CURRENT_DATE then start_date desc else
>     ...
>
> If I remove the "desc" and "asc" then the quey is accepted but doesn't
> do what I want.
>
> OTOH if I try:
>
>     critik=# select start_date from show_date order by start_date case when start_date > CURRENT_DATE then desc else
ascend; 
>     ERROR:  syntax error at or near "case"
>     LINE 1: ...ect start_date from show_date order by start_date case when ...
>
> How can i order ASC or DESC depending on a condition?
>
> Thanks,
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
Correction:


# 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;

Re: CASE in ORDER BY clause

From
Louis-David Mitterrand
Date:
On Fri, Jul 06, 2007 at 08:02:54PM +0400, Viatcheslav Kalinin wrote:
> Louis-David Mitterrand 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;

But... this works!

Many thanks,

Re: CASE in ORDER BY clause

From
Louis-David Mitterrand
Date:
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;

The strange thing is when I try:

    select start_date from show_date order by case when start_date > CURRENT_DATE then 'start_date asc' else
'start_datedesc ' end; 

It lists start_date's without ordering them (does nothing).

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

Bug? Inconsistency?

Re: CASE in ORDER BY clause

From
Gregory Stark
Date:
"Louis-David Mitterrand" <vindex+lists-pgsql-general@apartia.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

Re: CASE in ORDER BY clause

From
Perry Smith
Date:
> 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).  Otherwise you have either:

... order by start_date desc, asc;

or

... order by desc, start_date asc;

This is what I would expect the syntax to be:

# select start_date from show_date
# order by
# case when start_date > CURRENT_DATE then start_date desc end,
# case when start_date <= CURRENT_DATE then start_date asc end;

Or, it seems like you could do:

# select start_date from show_date
# order by start_date
# case when start_date > CURRENT_DATE then desc end,
# case when start_date <= CURRENT_DATE then asc end;

Re: CASE in ORDER BY clause

From
Tom Lane
Date:
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.

            regards, tom lane

Re: CASE in ORDER BY clause

From
Martijn van Oosterhout
Date:
On Sat, Jul 07, 2007 at 01:49:09PM -0500, Perry Smith 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).  Otherwise you have either:

The keyword asc/desc applies to an expression, the result is not an
expression, hence you cannot put the asc/desc inside a case.

> ... order by start_date desc, asc;
> or
> ... order by desc, start_date asc;

Almost, it's actually:
 ... order by start_date desc, null asc;
 or
 ... order by null desc, start_date asc;

Ordering by a constant has no effect, which is why it works.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment

Re: CASE in ORDER BY clause

From
Perry Smith
Date:
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;

Re: CASE in ORDER BY clause

From
Lew
Date:
Perry Smith wrote:
> I see.  So, in effect he has:
>
>     ORDER BY NULL DESC, start_date ASC;
>
> or
>
>   ORDER BY start_date DESC, NULL ASC;

Not exactly.  He has (first sort clause) *and* (second sort clause), not "or".

Both sort clauses operate at all rows.

You are sort of correct in that for any given row, its position in the SELECT
order will be determined by exactly one of

(start_date, NULL) for future dates
or
(NULL, start_date) for past dates
.

It is incorrect to see that as two separate ORDER BY clauses.

So if your RDBMS sorts NULLs after all other values, then from
>> 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;

all rows with start_date > CURRENT_DATE will appear first, in start_date
descending order,
then all rows with start_date <= CURRENT_DATE will appear, in start_date
ascending order.

Is CURRENT_DATE evaluated once for the query or twice for each row?

--
Lew

Re: CASE in ORDER BY clause

From
"Uwe C. Schroeder"
Date:
On Saturday 07 July 2007, Lew wrote:

> So if your RDBMS sorts NULLs after all other values, then from
>
> >> 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;
>
> all rows with start_date > CURRENT_DATE will appear first, in start_date
> descending order,
> then all rows with start_date <= CURRENT_DATE will appear, in start_date
> ascending order.
>
> Is CURRENT_DATE evaluated once for the query or twice for each row?

CURRENT_DATE is evaluated once per transaction.  If you run in autocommit -
mode, then the single query is wrapped in a transaction by itself.
Either way it's never evaluated per occurrence.

    Uwe

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

Re: CASE in ORDER BY clause

From
Tom Allison
Date:
Uwe C. Schroeder wrote:
>
> On Saturday 07 July 2007, Lew wrote:
>
>> So if your RDBMS sorts NULLs after all other values, then from
>>
>>>> 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;
>> all rows with start_date > CURRENT_DATE will appear first, in start_date
>> descending order,
>> then all rows with start_date <= CURRENT_DATE will appear, in start_date
>> ascending order.
>>
>> Is CURRENT_DATE evaluated once for the query or twice for each row?
>
> CURRENT_DATE is evaluated once per transaction.  If you run in autocommit -
> mode, then the single query is wrapped in a transaction by itself.
> Either way it's never evaluated per occurrence.
>

I'm coming in late on this but you might try something like...

select ... from (
select
...
case when start_date > current date
     then 1 || start_date - current_date
     else 0 || current_date - start_date end "FOO"
)
order by FOO desc

Or something like that...