Re: sort character data in arbitrary order?

On Saturday 14 January 2006 14:10, Michael Fuhr wrote:
> On Sat, Jan 14, 2006 at 01:38:52PM -0600, Robert Paulsen wrote:
> >     SELECT foo, bar, baz, FROM my_table WHERE state ~ '[abc]'
> >     ORDER BY state ASC LIMIT 1.
> >
> > This works as expected. My problem is that I am relying on the collating
> > sequence of the letters a-z and the desirability of states may not always
> > be in this order.
>
> How do you determine desirability?  You could order by an expression
> that evaluates to a state's desirability.

I don't determine the desirability. That is outside of my control. Today it is
a>b>c but tomorrrow it might be r>g>x. I generate the query with a perl
script and can modify the script query to suit the current conditions. I just
need to come up with the basic structure of the query. The one I have works
but only because a>b>c matches the collating sequence of the alphabet.

In another reply to my question Andrew came up with something I think I can
use -- another table that maps state characters to numeric values that can be
used in the ORDER BY part of the query.