On Saturday 14 January 2006 14:06, Andrew - Supernews wrote: > On 2006-01-14, Robert Paulsen <robert@xxxxxxxxxxxxxxxxx> 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 -------------------------------------------------------
