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.
