From: | Andrew - Supernews <andrew+nonews(at)supernews(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: sort character data in arbitrary order? |
Date: | 2006-01-14 20:06:40 |
Message-ID: | slrndsimeg.1rvr.andrew+nonews@atlantis.supernews.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2006-01-14, Robert Paulsen <robert(at)paulsenonline(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-01-14 20:10:07 | Re: sort character data in arbitrary order? |
Previous Message | brew | 2006-01-14 19:46:59 | Drupal and postgreSQL |