Question 136929 in PostgreSQL Question posted in
The official documentation can be found here.

Weird ordering behavior in postgresql

I’m trying to run the below query in the postgresql

SELECT module
FROM(
    VALUES ('Data Archiving/Back-Up/Recovery'), ('Database Management Software'), ('Data Warehousing/Mining')
) s(module) ORDER BY module ASC;

I’m expecting the order should look like below

module
Data Archiving/Back-Up/Recovery
Data Warehousing/Mining
Database Management Software

But it shows in below order

module
Data Archiving/Back-Up/Recovery
Database Management Software
Data Warehousing/Mining

Can someone help me understand this behavior?

Tags:

2

Answers


  1. 0

    There is a work around to achieve it by sorting your data by module with spaces replaced by the character 'a'.

    SELECT module
FROM(
    VALUES ('Data Archiving/Back-Up/Recovery'), ('Database Management Software'), ('Data Warehousing/Mining')
) s(module) ORDER BY replace(module, ' ', 'a') ASC;
    Login or Signup to reply.
  2. 0

    As mentioned in comment you should realy check the PostgreSQL COLLATE support (a search with "postgresql weird order" quickly directs you toward)

    Example

    select * from (values
('Data Warehouse'),
('Data Archive'),
('Database')
) t(txt)
order by 1

txt           |
--------------+
Data Archive  |
Database      |
Data Warehouse|


select * from (values
('Data Warehouse'),
('Data Archive'),
('Database')
) t(txt)
order by txt collate "C";

txt           |
--------------+
Data Archive  |
Data Warehouse|
Database      |
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search