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