Message-ID: <29713231d9baeab7921fc047d0d41252@news.teranews.com>
Date: Fri, 06 Feb 2004 16:17:47 GMT
Lines: 48
From: Jeff Boes <jboes@nexcerpt.com>
Subject: Re: Cool ORDER BY feature
Organization: Mur Consulting
References: <25d8d6a8.0402060649.59aab655@posting.google.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
To: pgsql-sql@postgresql.org

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