On 2006-01-14, Robert Paulsen <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
