Re: sort character data in arbitrary order?

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.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services