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
