Re: Cool ORDER BY feature

At some point in time, googlemike(at)hotpop(dot)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(at)qtm(dot)net | --Elbert Hubbard (1856-1915), American author