Postgresql - make the order by correct

Question

Feels like a silly question.

I have the following query

select 'Cardiac - ACS' as a
union 
select 'Cardiac - All' as a
order by a

I want the second row "Cardiac - All" appear first by replacing the 'hyphen' character. What should I replace it with? (and how do I find out the order of the symbols and their precedence in Postgres?)

Why would you want to replace the hyphen? Just get ORDER BY right. — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 27, 2014 at 7:46
e.g. you can do "select 'Cardiac 1 All' and that makes it come up first.. but that looks ugly... I want another symbol that can come first before "-"..... — ericbae
– ericbae, Commented Aug 27, 2014 at 7:48
So you are looking for a character that sorts before the hyphen? Sort order is dictated by collation rules and depends on your setting of the same (SHOW lc_collate to find out). — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 27, 2014 at 7:50
@ericbae what about select 'Cardiac __ ACS' as a union select 'Cardiac _ All' as a order by a ?? — Vivek S.
– Vivek S., Commented Aug 27, 2014 at 10:38

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Sort order of characters

Sort order depends on your locale, the collation in particular. Find your setting with:

SHOW lc_collate;

In the C locale, characters are sorted according to their byte values. These characters sort before the hyphen ('-'):

SELECT chr(g)
FROM generate_series (33,ascii('-')) g

chr
---
!
"
#
$
%
&
'
(
)
*
+
,
-

The same is not true for other collations. Test with:

SELECT g, chr(g)
FROM   generate_series (33,ascii('-')) g
order  by chr(g) COLLATE "C"

The hyphen has a decimal ASCII value of 45.
Remove COLLATE "C" to see the effect of your current locale.

To find basic candidate characters in your collation, run:

SELECT g, chr(g)
FROM   generate_series (1,255) g
ORDER  BY chr(g);

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 27, 2014 at 7:44

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ericbae Over a year ago

thanks for the help. very much appreciated. I did "SHOW lc_collate" and I got "en_US.UTF-8" and I did the above query and the hyphen came on top!!! looks like I have no other symbol to replace it with!

Erwin Brandstetter Over a year ago

@ericbae: Did you try the last query, too? Also, consider other ways to sort your data. Messing with values seems like a bad idea (depending on what you actually do). Consider @Clodoaldo's advice.

Clodoaldo Neto · Accepted Answer · 2014-08-27 18:12:17Z

If there are arbitrary sorting requirements then create a column to sort on:

select a, (left(a, position('-' in a)), arbitrary_order)
from (
    select 'Cardiac - ACS' as a, 9 as arbitrary_order
    union 
    select 'Cardiac - All', 0
) s
order by 2, 1
;
       a       |       row       
---------------+-----------------
 Cardiac - All | ("Cardiac -",0)
 Cardiac - ACS | ("Cardiac -",9)

The default for the arbitrary_order column will be something like 9 or 999 and the arbitrary cases will have lower values.

Or test for the existence of the '- All' string

select a, (
    left(a, position('-' in a)),
    substring(a from position('-' in a)) != '- All'
    )
from (
    select 'Cardiac - ACS' as a
    union 
    select 'Cardiac - All'
) s
order by 2, 1
;
       a       |       row       
---------------+-----------------
 Cardiac - All | ("Cardiac -",f)
 Cardiac - ACS | ("Cardiac -",t)

Collectives™ on Stack Overflow

Postgresql - make the order by correct

2 Answers 2

Sort order of characters

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Sort order of characters

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related