I have a sort problem in PostgreSQL with below data:
name
-----
@CF
@CG
CD
CE
I used select name from table order by name, the result as below:
name
-----
CD
CE
@CF
@CE
It seems that Postgres just ignores the special character @ and sorts the left string. However, I'd like it sorted like this:
name
-----
@CF
@CG
CD
CE
Searching the internet didn't help. I hope someone here could give a suggestion.
Use PostgreSQL's collation support to tell it you want a particular collation.
Given:
CREATE TABLE t AS VALUES ('CD'),('CE'),('@CF'),('@CE');
You can force byte-wise collation using:
SELECT * FROM t ORDER BY column1 COLLATE "C";
The "C" collation is a byte-wise collation that ignores national language rules, encoding, etc.
Just add that to the order by clause:
ORDER BY CASE WHEN LEFT(name,1) = '@' THEN 0 ELSE 1 END ASC, name ASC
Your undisclosed collation setting obviously ignores the @ character for sort order. Either switch to another collation as suggested by @Craig. Or, if you want to stick to your collation for the rest of the string, add a special case for leading @.
In Postgres you can order by boolean values directly. Default order is FALSE, TRUE, NULL.
ORDER BY name !~~ '@%', name
!~~ being the Postgres shorthand for NOT LIKE.
Use this:
SELECT name
FROM table
ORDER BY name WHEN LEFT(name, 1) = '@'
THEN 0 ELSE 1 END, name
