From: | Robert Paulsen <robert(at)paulsenonline(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | sort character data in arbitrary order? |
Date: | 2006-01-14 19:38:52 |
Message-ID: | 200601141338.53068.robert@paulsenonline.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | brew | 2006-01-14 19:46:59 | Drupal and postgreSQL |
Previous Message | Jonel Rienton | 2006-01-14 19:32:22 | Upgrading 8.1.1 to 8.1.2 in Windows |