pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

(repost) Help understanding expressions in order by clause
2+ messages / 2 participants
[nested] [flat]

* (repost) Help understanding expressions in order by clause
@ 2007-10-25 16:44 Steve Midgley <public@misuse.org>
  2007-10-25 17:03 ` Re: (repost) Help understanding expressions in order by clause Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Steve Midgley @ 2007-10-25 16:44 UTC (permalink / raw)
  To: 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@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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: (repost) Help understanding expressions in order by clause
  2007-10-25 16:44 (repost) Help understanding expressions in order by clause Steve Midgley <public@misuse.org>
@ 2007-10-25 17:03 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2007-10-25 17:03 UTC (permalink / raw)
  To: Steve Midgley <public@misuse.org>; +Cc: pgsql-sql

Steve Midgley <public@misuse.org> writes:
> 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?

These expressions yield boolean true or false, and false sorts before true.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2007-10-25 17:03 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-25 16:44 (repost) Help understanding expressions in order by clause Steve Midgley <public@misuse.org>
2007-10-25 17:03 ` Tom Lane <tgl@sss.pgh.pa.us>

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