Concatenation combining with ORDER BY

Question

I have problems combining concatenation with order in Postgre (9.1.9). Say I have a table border with three 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 country codes, and the third is the length of the border between these countries.
The primary key is defined for the first two fields.

I need to make a choice of a column that will have unique values ​​for the entire table, in addition, this column must be selected in descending order. To do this, I combine the key fields with the separator character, otherwise two different lines can give the same result as (AB, C and A, BC).

So, I run the following query:

 select country1||'_'||country2 from borders order by 1; 

However, as a result, I see that the "_" character is excluded from sorting. The results look 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 `_ 'did not exist in the lines.

If I use the letter (say, "x") as a separator, the order is correct. But I have to use a special character that does not appear in the country1 and country2 fields to avoid claims.

What should I do to take into account the symbol "_" during sorting.

---

EDIT

It turned out that concatenation has nothing to do with the problem. The problem is that the order simply ignores the "_" character.

Answer 1

 select country1 || '_' || country2 collate "C" as a from borders order by 1 

demo sql

Notes as discussed in the comments:

1.) COLLATE "C" is used in an ORDER BY if it refers to an expression in a SELECT by a positional parameter or alias. If you repeat the expression in ORDER BY , you also need to repeat the COLLATE if you want to influence the sort order accordingly.

demo sql

2.) In collaborations where _ does not affect the sorting order, it is more efficient to use a foggy query , especially since it uses an existing index ( primary key is defined on the first two fields ).
However, if _ has an effect, you need to sort by the combined expression:

demo sql

Request performance (tested in Postgres 9.2):
demo sql

PostgreSQL sorting support in the manual.

Answer 2

Just sort by two columns:

 SELECT country1||'_'||country2 FROM borders ORDER BY country1, country2; 

If you do not use aggregates or windows, PostgreSQL allows you to order by columns, even if you do not include them in the SELECT list.

As suggested in another answer, you can also change the sorting of a combined column, but if you can, sorting by equal columns is faster, especially if you have a pointer to them.

Answer 3

What happens if you do the following?

  select country1||'_'||country2 from borders order by country1||'_'||country2 

My knowledge in order of 1 only sorted in order. It will not do anything on concatenated columns. Of course, I'm talking from SQL Server knowledge, so let me know if I leave the database.

Edited: Good; just saw a Parado post, as I posted mine. Perhaps you could create a view from this query (specify a column name) and then request a view, sort by this column? Or follow these steps:

 select country_group from ( select country1||'_'||country2 as country_group from borders ) a order by country_group