SQL Query ORDER BY not working as deisred
Posted on 2006-11-20
I have a PostgreSQL database using the UTF8 character set. I have a field name, which has data such as
When I run a query
ORDER BY name
I get the results in the order above. What I would like is data properly ordered as
Since the comma has a lower ASCII value than all upper and lower case alpha, it should sort the second way, but PostgreSQL isn't doing it. I know it's doing this because the comma is being ignored in the sort. I know I could split last name and first name into separate fields, but this doesn't solve other issues I have with other database fields, where the same problem manifests itself. So these are the constraints:
Must use UTF8 character set
Must sort the second way (above)
Cannot split the field into multiple fields
Can't do any work arounds like temp tables, etc.
I suppose there's a parameter setting that would allow this to sort properly, but I couldn't find it in the documentation.