Thread: sort character data in arbitrary order?

Hi,

I need to query a database for a record with the "best" value in a
one-character field. The field is named "state" and I need a record with a
state of 'a', 'b', or 'c'. There may be more than one matching record but I
want the "best" one where "best" is defined as state 'a', or if there are no
'a' records, state 'b', etc.

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.

Note that I only have limited ability to change the structure of the database.
I probably could if there was no other way but it would be very disruptive if
I did so.

Bob

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

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.

--
Michael Fuhr

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.