(repost) Help understanding expressions in order by clause

From: Steve Midgley <public(at)misuse(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: (repost) Help understanding expressions in order by clause
Date: 2007-10-25 16:44:36
Message-ID: 20071025164917.D310A9F9251@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)abc(dot)com');
insert into test_order (email)
values ('def(at)abc(dot)com');
insert into test_order (email)
values ('hij(at)abc(dot)com');
insert into test_order (email)
values ('klm(at)abc(dot)com');
insert into test_order (email)
values ('123(at)abc(dot)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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bricklen Anderson 2007-10-25 16:57:49 Re: request for help with COPY syntax
Previous Message Chuck D. 2007-10-25 16:16:01 Re: request for help with COPY syntax