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.
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
-------------------------------------------------------
Import Notes
Resolved by subject fallback