Combining concatenation with order by in psql
I have troubles in combining concatenation with order by in Postgre
(9.1.9). Let's say, I have a table borders with 3 fields:
Table "borders"
Column | Type | Modifiers
---------------+----------------------+-----------
country1 | character varying(4) | not null
country2 | character varying(4) | not null
length | numeric |
The first two fields are codes of the countries and the third one is the
length of the border among those countries. The primary key is defined on
the first two fields.
I need to compose a select of a column that would have unique values for
the whole table, in addition this column should be selected in decreasing
order. For this I concatenate the key fields with a separator character,
otherwise two different rows might give same result, like (AB, C and A,
BC).
So I run the following query:
select country1||'_'||country2 from borders order by 1;
However in the result I see that the '_' character is omited from the
sorting. The results looks like this:
?column?
----------
A_CH
A_CZ
A_D
AFG_IR
AFG_PK
AFG_TAD
AFG_TJ
AFG_TM
AFG_UZB
A_FL
A_H
A_I
.
.
You can see that the result is sorted as if '_' doesn't exists in the
strings.
What should I do, in order to make the '_' character to be taken into
account during the sorting.
Thanks in advance.
No comments:
Post a Comment