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