From: Steve Midgley <public@misuse.org>
To: pgsql-sql@postgresql.org
Subject: (repost) Help understanding expressions in order by clause
Date: Thu, 25 Oct 2007 09:44:36 -0700
Message-ID: <20071025164917.D310A9F9251@postgresql.org> (raw)
In-Reply-To: <20071024172620.4ECD2607787@mx1.hub.org>
References: <20071024172620.4ECD2607787@mx1.hub.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: public@misuse.org
Subject: Re: (repost) Help understanding expressions in order by clause
In-Reply-To: <20071025164917.D310A9F9251@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox