CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
ericbae
ericbae

Reputation: 9644

Postgresql - make the order by correct

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?)

Upvotes: 1

Views: 476

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125544

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)

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659247

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);

Upvotes: 1

Related Questions