Re: [GENERAL] CASE in ORDER BY clause

2007-07-20 Thread Tom Allison
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

Re: [GENERAL] CASE in ORDER BY clause

2007-07-09 Thread Uwe C. Schroeder
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; > >

Re: [GENERAL] CASE in ORDER BY clause

2007-07-09 Thread Lew
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 gi

Re: [GENERAL] CASE in ORDER BY clause

2007-07-07 Thread Perry Smith
On Jul 7, 2007, at 2:15 PM, Tom Lane wrote: Perry Smith <[EMAIL PROTECTED]> 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 <= CURR

Re: [GENERAL] CASE in ORDER BY clause

2007-07-07 Thread Martijn van Oosterhout
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 m

Re: [GENERAL] CASE in ORDER BY clause

2007-07-07 Thread Tom Lane
Perry Smith <[EMAIL PROTECTED]> 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; > >

Re: [GENERAL] CASE in ORDER BY clause

2007-07-07 Thread Perry Smith
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

Re: [GENERAL] CASE in ORDER BY clause

2007-07-07 Thread Gregory Stark
"Louis-David Mitterrand" <[EMAIL PROTECTED]> 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

Re: [GENERAL] CASE in ORDER BY clause

2007-07-07 Thread Louis-David Mitterrand
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: sel

Re: [GENERAL] CASE in ORDER BY clause

2007-07-07 Thread Louis-David Mitterrand
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 w

Re: [GENERAL] CASE in ORDER BY clause

2007-07-06 Thread Viatcheslav Kalinin
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_date asc end; ERROR: syntax error at or near "desc" LINE 1: ...se when start_date > CURRENT_DATE

Re: [GENERAL] CASE in ORDER BY clause

2007-07-06 Thread Viatcheslav Kalinin
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_date asc end; ERROR: syntax error at or near "desc" LINE 1: ...se when start_date > CURRENT_DATE