From: Jeff Boes <jboes@nexcerpt.com>
To: pgsql-sql@postgresql.org
Subject: Re: Cool ORDER BY feature
Date: Fri, 06 Feb 2004 16:17:47 GMT
Message-ID: <29713231d9baeab7921fc047d0d41252@news.teranews.com> (raw)
References: <25d8d6a8.0402060649.59aab655@posting.google.com>
At some point in time, googlemike@hotpop.com (Google Mike) wrote:
>Create a priority column in a test table and add the entries, "HI",
>"N", and "LO" in sequence like that for up to, say, 15 rows. Now
>select this and order by priority. You'll notice that it goes in "HI",
>"LO", and "N" order. A more preferrable option would to sort this as
>"HI", "N", and "LO". Now do something like this:
>
>select * from testtable order by priority = 'LO', priority = 'N',
>priority = 'HI'
>
>Guess what! It sorts the priorities properly without you having to add
>another column that uses something like a numerical sort index.
Not that your way is wrong, but just to illustrate that, as we say in Perl,
"There's more than one way to do it!":
select * from testtable order by
CASE WHEN priority='LO' THEN 1
WHEN priority='N' THEN 2
WHEN priority='HI' THEN 3
ELSE 4 END;
For more complex items, you could write a stored procedure that translates to a
sort order. Or something like this:
create table priorities as
select 'LO' as priority, 1 as sort_order
union
select 'N', 2
union
select 'HI', 3;
select * from testtable join priorities using (priority)
order by sort_order;
This last approach is what I've used with code-tables in our system, because it
allows you to change the global sort ordering in just one place without having
to affect code.
--
~~~~~~~~~~~~~~~~| Genius may have its limitations, but stupidity is not
Jeff Boes | thus handicapped.
jboes@qtm.net | --Elbert Hubbard (1856-1915), American author
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: jboes@nexcerpt.com
Subject: Re: Cool ORDER BY feature
In-Reply-To: <29713231d9baeab7921fc047d0d41252@news.teranews.com>
* 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