We have a column in a PostgreSQL table with the following data:

1A
1ABCD
1B
ABCD1
2
ABCD2
3
ABCD3
ABCD4
ABCD5
ABCD10
ABCD11
ABCD1111
A
B 

Expected result after sorting:

1
2
3
1A
1ABCD
1B
A
B
ABCD1
ABCD2
ABCD3
ABCD4
ABCD5
ABCD10
ABCD11
ABCD1111

We tried doing normal DESC and '([0-9] )')::BIGINT ASC. But not getting expected sorted results.

CodePudding user response：

So you can try this just put column name instead of asterisk and column.

SELECT * FROM PostgreSQL ORDER BY column ASC;

CodePudding user response：

Mike O's comment is correct if you have a typo in where you would like B sorted in the mix. However, if you have some complex logic (ie, not truly lexicographical order) and you really want B sorted before ABCD1, then you could use a case statement to help your sorts:

select col1, 
case 
  when col1 ~* '[a-z]' is false then 1 --numeric only
  when substring(col1, 1, 1) ~* '[0-9]' is true then 2 --starts with number
  when col1 ~* '[0-9]' is false then 3 --letters only
  else 4 --alphanumeric mixed
end sort1
from my_table
order by 2,1

Output:

DB-fiddle found here.