The ORDER BY clause

The ORDER BY clause sorts retrieved rows by the values that are contained in a column or set of columns.

When this clause sorts character columns, the results of the sort depend on the data type of the column, as follows:
  • Columns that are sorted in code-set order:
    • CHAR
    • LVARCHAR
    • VARCHAR
  • NCHAR and NVARCHAR columns are sorted in localized order.
Assume that you use a nondefault locale for the client and database locale, and you make a query against the table called abonnés. This SELECT statement specifies three columns of CHAR data type in the select list: numéro (employee number), nom (family name), and prénom (given name).
SELECT numéro,nom,prénom 
   FROM abonnés
   ORDER BY nom;

The statement sorts the query results by the values that are contained in the nom column. Because the nom column that is specified in the ORDER BY clause is a CHAR column, the database server sorts the query results in the code-set order.

As this table shows, names that begin with uppercase letters come before names beginning with lowercase letters, and names that begin with an accented letter (Ålesund, Étaix, Ötker, and Øverst) are at the end of the list.
Table 1. Data set for code-set order of the abonnés table
numéro nom prénom
13612 Azevedo Edouardo Freire
13606 Dupré Michéle Françoise
13607 Hammer Gerhard
13602 Hämmerle Greta
13604 LaForêt Jean-Nol
13610 LeMatre Hélose
13613 Llanero Gloria Dolores
13603 Montaña José Antonio
13611 Oatfield Emily
13609 Tiramis Paolo Alfredo
13600 da Sousa João Lourenço Antunes
13615 di Girolamo Giuseppe
13601 Ålesund Sverre
13608 Étaix Émile
13605 Ötker Hans-Jrgen
13614 Øverst Per-Anders

Results of the query are different, however, if the numéro, nom, and prénom columns of the abonnés table are defined as NCHAR rather than CHAR.

Suppose the nondefault locale defines a localized order that collates the data as the following table shows. This localized order defines equivalence classes for uppercase and lowercase letters and for unaccented and accented versions of the same letter.
Table 2. Data set for localized order of the abonnés table
numéro nom prénom
13612 Azevedo Edouardo Freire
13601 Ålesund Sverre
13600 da Sousa João Lourenço Antunes
13615 di Girolamo Giuseppe
13606 Dupré Michéle Françoise
13608 Étaix Émile
13607 Hammer Gerhard
13602 Hämmerle Greta
13604 LaForêt Jean-Nol
13610 LeMatre Hélose
13613 Llanero Gloria Dolores
13603 Montaña José Antonio
13611 Oatfield Emily
13605 Ötker Hans-Jrgen
13614 Øverst Per-Anders
13609 Tiramis Paolo Alfredo

The same SELECT statement now returns the query results in localized order because the nom column that the ORDER BY clause specifies is an NCHAR column.

The SELECT statement supports use of a column substring in an ORDER BY clause. However, you need to ensure that this use for column substrings works with the code set that your locale supports. For more information, see Partial characters in column substrings.