Date: Thu, 25 Oct 2007 09:44:36 -0700
To: pgsql-sql@postgresql.org
From: Steve Midgley <public@misuse.org>
Subject: (repost) Help understanding expressions in order by clause
In-Reply-To: <20071024172620.4ECD2607787@mx1.hub.org>
References: <20071024172620.4ECD2607787@mx1.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
Message-Id: <20071025164917.D310A9F9251@postgresql.org>

Hi,

(I posted this to the list on 10/25 but it didn't seem to get 
distributed - apologies if it did and I'm actually double posting right 
now..)

I've read on this list about some pretty powerful examples of using 
expressions in order by clauses to sort according to very complex 
rules. I kind of half "get it" and was playing around with it today to 
learn more. I'm hoping for one or two additional pointers from the 
resident experts..

Take this sample:

--sql start
drop table if exists test_order;
create temp table test_order
(
  id serial,
  email varchar(255)
);
insert into test_order (email)
values ('abc@abc.com');
insert into test_order (email)
values ('def@abc.com');
insert into test_order (email)
values ('hij@abc.com');
insert into test_order (email)
values ('klm@abc.com');
insert into test_order (email)
values ('123@abc.com');

select * from test_order
order by id <> 3, email
--sql end

Putting the expression "id <> 3" causes the id of 3 to sort to the TOP 
of the list. This statement does the same thing:

select * from test_order
order by id = 3 DESC, email

I know there is a good, rational reason for this, and I'm hoping 
someone will explain it to me? From what I can see, true is sorting 
AFTER false in Postgres? In general, is it better/preferred just to use 
"case" statements to get the results I'm expecting?

Or maybe someone could point me to the relevant docs that explains 
order by behavior in more detail? I read this 
http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-ORDERBY 
and it didn't make things any clearer.

I'm quite interested in the power of "expressioned sort by's" and so I 
appreciate any assistance in this regard.

Thanks!

Steve