Fwd: Re: sort character data in arbitrary order?

On Saturday 14 January 2006 14:06, Andrew - Supernews wrote:
> On 2006-01-14, Robert Paulsen <robert@paulsenonline.net> wrote:
> > Here is my query so far:
> >
> >     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.
> >
> > Is there a better way to do the "ORDER BY" or some other way to
> > accomplish this? I know I could do three queries and then compare the
> > results but I was hoping to do this all within the single query.
>
> If there's only a small number of possible "state" values then:
>
> ORDER BY state = 'a' DESC, state = 'b' DESC, state = 'c' DESC
>
> If there's more than a small number, then have a separate state_priority
> table mapping states to integer values, and join against that and sort by
> the priority value.

Thanks! I think both of those solutions look good. I my case there will only
be a few states of interest so the first approach looks best.

Bob

-------------------------------------------------------