ORDER BY Clause
Purpose
Specifies how result sets are sorted. If you do not use this clause, result rows are returned unsorted.
Syntax
where:
Syntax Element … |
Specifies … |
ORDER BY |
the order in which result rows are to be sorted. You can specify one or more sort columns on expression, column_name, column_name_alias, or column_position. The first sort column you specify determines the column used for the primary (first) sort. Any additional sort columns you specify determines the potential subsequent sorting based on the order in which you specify the columns. When multiple sort columns are specified, rows are sorted that have the same values of the previous sort column (or columns). You can specify a sort column to be sorted in either ascending or descending order. |
expression |
a valid SQL expression on which to sort rows. If the sort field is a character string, the expression used in the ORDER BY phrase can include a type modifier to force the sort to be either CASESPECIFIC or NOT CASESPECIFIC. The expression you specify must not reference a column that has a BLOB or CLOB data type. |
column_name |
the name of a column on which to sort rows. An ordering column does not have to be specified as part of the select expression list. The maximum number of columns you can specify is 64. You can specify a column_name_alias instead of column_name (see the description of the column_name_alias element for details). The column_name_alias must not have the same name as a physical column in the table definition. If it does have the same name, you must specify column_position, not column_name_alias. See “Example 4: Ordering on a Column Name Alias” on page 233. The column you specify cannot have a BLOB or CLOB data type. |
column_name_ |
a column name alias specified in the select expression list of the query for the column on which the result rows are to be sorted. If you specify a column_name_alias, that alias cannot match the name of any column that is defined in the table definition for any table referenced in the FROM clause of the query, whether that column is specified in the select list or not. The system references the underlying physical column having the name rather than the column that you attempt to reference using that same name as its alias. If you attempt to specify such an improper column_name_alias, the system aborts the request and returns an error. The workaround for this is to specify the sort column by its column_position value within the select list for the query. See “Rules and Restrictions for the ORDER BY Clause” on page 223. |
column_position |
the numeric position of a column or expression specified in the select expression list on which to sort. The value you specify must be a positive constant integer literal with a value between 1 and the number of columns specified in the select list, inclusive. Note that Teradata Database treats macro and procedure parameters as expressions, not as the specification of a column position. If the column_name_alias specified in the select list is the same as the name of the physical column in the underlying table specified in the FROM clause, then you cannot specify that column_name_alias as a sort value. You can specify a column position reference to order on the column or expression in the select list. However, you cannot use a column_name_alias that is the same as the name of some other column specified in the table definition. Substituting column_position for column_name_alias when the query will not otherwise work is not a good practice, and is not recommended. However, it does work if you must resort to it for a given situation. The column or expression referenced by its numeric position in the select-list must not have either a BLOB or CLOB data type. |
ASC |
that the results are to be ordered in ascending sort order. If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session. The default order is ASC. |
DESC |
that the results are to be ordered in descending sort order. If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session. |
NULLS FIRST |
that NULL results are to be listed first. |
NULLS LAST |
that NULL results are to be listed last. |
ANSI Compliance
The ORDER BY clause is ANSI SQL:2011-compliant with extensions. The specification of an ORDER BY clause in the definition of an updatable cursor is ANSI SQL:2011‑compliant.
The specification of an expression as an ordering argument in the ORDER BY clause is a Teradata extension to the ANSI SQL:2011 standard.
Rules and Restrictions for the ORDER BY Clause
The rules and restrictions for the use of ORDER BY are:
You can also order on a column_name_alias.
The maximum number of column names you can specify is 64.
You cannot specify BLOB or CLOB columns in the column name list because you cannot sort on LOB values.
For details on defining case sensitivity for a column, see SQL Data Types and Literals.
You cannot specify BLOB or CLOB columns in the column position list because you cannot sort on LOB values.
When you specify both WITH and ORDER BY clauses in the same SELECT request, they function together as follows:
This is true regardless of the structure of the query or the number of WITH clauses. See “Example 3: Combining WITH and ORDER BY Clauses” on page 241.
Unexpected Results
This section describes some unexpected results that can occur when using the ORDER BY clause. They are:
Unexpected Row Length Errors: Sorting and Default Sort Order
Before performing the sort operation that orders the rows to be returned, Teradata Database creates a sort key and appends it to the rows. If the length of the sort key exceeds the system row length limit of 64 KB, the operation fails and returns an error. Depending on the situation, the error message text is one of the following.
For explanations of these messages, see Messages.
There are several possible reasons why a data row plus BYNET sort key might unexpectedly exceed the spool row size limit of 1 MB, even without including any updates.
See Database Design and SQL Data Types and Literals for details.
Note that this issue is not relevant if your system uses the packed64 row architecture rather than the aligned row format architecture. For details, see Database Design.
The BYNET only looks at the first 4096 bytes of the sort key created to sort the specified fields, so if the field the sort key is based on is greater than 4096 bytes, the key is truncated and the data may not sort in the desired order.
By default, nulls sort before all other data in a column. In other words, nulls sort low. For example, suppose you want to sort the following set of values: 5, 3, null, 1, 4, and 2.
The values sort as follows:
null
1
2
3
4
5
By default, the result values of a character expression are sorted in ascending order, using the collating sequence in effect for the session.
Unexpected Sort Order When Querying DATE Data Type Columns
Using the ORDER BY clause to query columns with the DATE data type may yield results in which the order of the returned values seem to be out of order.
The factors that contribute to this type of unexpected result are:
For possible workarounds, see “Workarounds” on page 227.
Example of Unexpected Sort Order
This example shows a result set of TIME data type column values that do not seem to be sorted in the order specified in the ORDER BY clause. The local time values inserted into the column are followed by the values returned by the query.
Note: The time zone in this example is Pacific Time, which has a +8 hour time zone offset.
CREATE TABLE table_1
(column_1 INTEGER,
column_2 TIME);
INSERT INTO table_1 VALUEs ( 0, TIME'00:00:00.000000');
INSERT INTO table_1 VALUEs ( 1, TIME'01:00:00.000000');
INSERT INTO table_1 VALUEs ( 2, TIME'02:00:00.000000');
INSERT INTO table_1 VALUEs ( 3, TIME'03:00:00.000000');
INSERT INTO table_1 VALUEs ( 4, TIME'04:00:00.000000');
INSERT INTO table_1 VALUEs ( 5, TIME'05:00:00.000000');
INSERT INTO table_1 VALUEs ( 6, TIME'06:00:00.000000');
INSERT INTO table_1 VALUEs ( 7, TIME'07:00:00.000000');
INSERT INTO table_1 VALUEs ( 8, TIME'08:00:00.000000');
INSERT INTO table_1 VALUEs ( 9, TIME'09:00:00.000000');
INSERT INTO table_1 VALUEs (10, TIME'10:00:00.000000');
INSERT INTO table_1 VALUEs (11, TIME'11:00:00.000000');
INSERT INTO table_1 VALUEs (12, TIME'12:00:00.000000');
INSERT INTO table_1 VALUEs (13, TIME'13:00:00.000000');
INSERT INTO table_1 VALUEs (14, TIME'14:00:00.000000');
INSERT INTO table_1 VALUEs (15, TIME'15:00:00.000000');
INSERT INTO table_1 VALUEs (16, TIME'16:00:00.000000');
INSERT INTO table_1 VALUEs (17, TIME'17:00:00.000000');
INSERT INTO table_1 VALUEs (18, TIME'18:00:00.000000');
INSERT INTO table_1 VALUEs (19, TIME'19:00:00.000000');
INSERT INTO table_1 VALUEs (20, TIME'20:00:00.000000');
INSERT INTO table_1 VALUEs (21, TIME'21:00:00.000000');
INSERT INTO table_1 VALUEs (22, TIME'22:00:00.000000');
INSERT INTO table_1 VALUEs (23, TIME'23:00:00.000000');
This SELECT statement is used to query the time values in the TIME data type column (column_2).
SELECT * FROM table_1
ORDER BY column_2;
column_1 column_2
-------- ---------------
16 16:00:00.000000
17 17:00:00.000000
18 18:00:00.000000
19 19:00:00.000000
20 20:00:00.000000
21 21:00:00.000000
22 22:00:00.000000
23 23:00:00.000000
0 00:00:00.000000
1 01:00:00.000000
2 02:00:00.000000
3 03:00:00.000000
4 04:00:00.000000
5 05:00:00.000000
6 06:00:00.000000
7 07:00:00.000000
8 08:00:00.000000
9 09:00:00.000000
10 10:00:00.000000
11 11:00:00.000000
12 12:00:00.000000
13 13:00:00.000000
14 14:00:00.000000
15 15:00:00.000000
Because the sort order is based on UTC time (which is not visible in the column), the time values do not agree with the resulting sort order. Rows 16 through 23 appear at the top of the list because they belong at the top of the list in terms of UTC time.
The UTC equivalents for the returned time values are:
Returned Values UTC Equivalents (local time + 8 hours)
column_1 column_2
-------- -------------
16 16:00:00.000000 00:00:00.000000
17 17:00:00.000000 01:00:00.000000
18 18:00:00.000000 02:00:00.000000
19 19:00:00.000000 03:00:00.000000
20 20:00:00.000000 04:00:00.000000
21 21:00:00.000000 05:00:00.000000
22 22:00:00.000000 06:00:00.000000
23 23:00:00.000000 07:00:00.000000
0 00:00:00.000000 08:00:00.000000
1 01:00:00.000000 09:00:00.000000
2 02:00:00.000000 10:00:00.000000
3 03:00:00.000000 11:00:00.000000
4 04:00:00.000000 12:00:00.000000
5 05:00:00.000000 13:00:00.000000
6 06:00:00.000000 14:00:00.000000
7 07:00:00.000000 15:00:00.000000
8 08:00:00.000000 16:00:00.000000
9 09:00:00.000000 17:00:00.000000
10 10:00:00.000000 18:00:00.000000
11 11:00:00.000000 19:00:00.000000
12 12:00:00.000000 20:00:00.000000
13 13:00:00.000000 21:00:00.000000
14 14:00:00.000000 22:00:00.000000
15 15:00:00.000000 23:00:00.000000
Workarounds
These workarounds can be used to get expected results:
Use INTERVAL
SELECT column_1, column_2, column_2 - interval '8' hour
FROM table_1
ORDER BY 3;
column_1 column_2 (column_2- 8)
-------- -------------- --------------
0 00:00:00.000000 16:00:00.000000
1 01:00:00.000000 17:00:00.000000
2 02:00:00.000000 18:00:00.000000
3 03:00:00.000000 19:00:00.000000
4 04:00:00.000000 20:00:00.000000
5 05:00:00.000000 21:00:00.000000
6 06:00:00.000000 22:00:00.000000
7 07:00:00.000000 23:00:00.000000
8 08:00:00.000000 00:00:00.000000
9 09:00:00.000000 01:00:00.000000
10 10:00:00.000000 02:00:00.000000
11 11:00:00.000000 03:00:00.000000
12 12:00:00.000000 04:00:00.000000
13 13:00:00.000000 05:00:00.000000
14 14:00:00.000000 06:00:00.000000
15 15:00:00.000000 07:00:00.000000
16 16:00:00.000000 08:00:00.000000
17 17:00:00.000000 09:00:00.000000
18 18:00:00.000000 10:00:00.000000
19 19:00:00.000000 11:00:00.000000
20 20:00:00.000000 12:00:00.000000
21 21:00:00.000000 13:00:00.000000
22 22:00:00.000000 14:00:00.000000
23 23:00:00.000000 15:00:00.000000
Use CAST to CHAR or VARCHAR
SELECT column_1, column_2, CAST(column_2 AS CHAR(15))
FROM table_1
ORDER BY 3;
column_1 column_2 column_2
-------- ------------ ---------------
0 00:00:00.000000 00:00:00.000000
1 01:00:00.000000 01:00:00.000000
2 02:00:00.000000 02:00:00.000000
3 03:00:00.000000 03:00:00.000000
4 04:00:00.000000 04:00:00.000000
5 05:00:00.000000 05:00:00.000000
6 06:00:00.000000 06:00:00.000000
7 07:00:00.000000 07:00:00.000000
8 08:00:00.000000 08:00:00.000000
9 09:00:00.000000 09:00:00.000000
10 10:00:00.000000 10:00:00.000000
11 11:00:00.000000 11:00:00.000000
12 12:00:00.000000 12:00:00.000000
13 13:00:00.000000 13:00:00.000000
14 14:00:00.000000 14:00:00.000000
15 15:00:00.000000 15:00:00.000000
16 16:00:00.000000 16:00:00.000000
17 17:00:00.000000 17:00:00.000000
18 18:00:00.000000 18:00:00.000000
19 19:00:00.000000 19:00:00.000000
20 20:00:00.000000 20:00:00.000000
21 21:00:00.000000 21:00:00.000000
22 22:00:00.000000 22:00:00.000000
23 23:00:00.000000 23:00:00.000000
Use TIMESTAMP instead of TIME
CREATE TABLE table_2
(column_1 INTEGER,
column_2 TIMESTAMP);
INSERT INTO table_2 SELECT * FROM table_1;
SELECT * FROM table_2
ORDER BY column_2;
column_1 column_2
-------- --------------------------
0 2011-11-07 00:00:00.000000
1 2011-11-07 01:00:00.000000
2 2011-11-07 02:00:00.000000
3 2011-11-07 03:00:00.000000
4 2011-11-07 04:00:00.000000
5 2011-11-07 05:00:00.000000
6 2011-11-07 06:00:00.000000
7 2011-11-07 07:00:00.000000
8 2011-11-07 08:00:00.000000
9 2011-11-07 09:00:00.000000
10 2011-11-07 10:00:00.000000
11 2011-11-07 11:00:00.000000
12 2011-11-07 12:00:00.000000
13 2011-11-07 13:00:00.000000
14 2011-11-07 14:00:00.000000
15 2011-11-07 15:00:00.000000
16 2011-11-07 16:00:00.000000
17 2011-11-07 17:00:00.000000
18 2011-11-07 18:00:00.000000
19 2011-11-07 19:00:00.000000
20 2011-11-07 20:00:00.000000
21 2011-11-07 21:00:00.000000
22 2011-11-07 22:00:00.000000
23 2011-11-07 23:00:00.000000
Specifying Collation
Different languages use different collation sequences, and you might need to alter your system collation to ensure that the ORDER BY clauses you specify in your queries sort correctly.
You can specify the default collation for a user using the CREATE USER or MODIFY USER statement, or you can specify collation for the duration of a session using the SET SESSION COLLATION statement. Otherwise, collation for a session is determined by the logon client system. The direction of the sort can be controlled by including the DESC (descending) or ASC (ascending) sort option in the SQL request.
Teradata Database supports ASCII, EBCDIC, and MULTINATIONAL collating sequences. If MULTINATIONAL is in effect, your collation will be one of the European (diacritical) or Kanji sort sequences described in “International Sort Orders” on page 231.
The following topics explain the results of ORDER BY as affected by whether character string expressions have the CASESPECIFIC or NOTCASESPECIFIC attribute.
Japanese Character Sort Order Considerations
If character strings are to be sorted NOT CASESPECIFIC, which is the default in Teradata session mode, only lowercase simple letters (for example, the characters a … z in the Latin alphabet) are converted to uppercase before a comparison or sorting operation is done.
The important thing to understood is that case differences are ignored in NOT CASESPECIFIC collations. In other words, the case of the characters is not critical for NOT CASESPECIFIC collation.
Any non-Latin single‑byte character, any multibyte character, and any byte indicating a transition between single‑byte characters and multibyte characters is excluded from this function.
If the character strings are to be sorted CASESPECIFIC, which is the default in ANSI session mode, then the case of the characters is critical for collation.
For Kanji1 character data in CASESPECIFIC mode, the letters in any alphabet that uses casing, such as Latin, Greek, or Cyrillic, are considered to be matched only if they are the identical letters and have the same case.
The system does not consider FULLWIDTH and HALFWIDTH characters to be matched whether in CASESPECIFIC or NOT CASESPECIFIC mode.
Teradata Database uses one of four server character sets to support Japanese characters:
Caution:
In accordance with Teradata internationalization plans, KANJI1 support is deprecated and is to be discontinued in the near future. KANJI1 is not allowed as a default character set; the system changes the KANJI1 default character set to the UNICODE character set. Creation of new KANJI1 objects is highly restricted. Although many KANJI1 queries and applications may continue to operate, sites using KANJI1 should convert to another character set as soon as possible. “”
If you are at a Japanese character site and you want to ensure that sorting of character data is as close as possible to that of the client, you should set your session collation (see “SET SESSION COLLATION” in SQL Data Definition Language) as follows:
For character data stored on the Teradata platform as either KanjiSJIS or Unicode, the CHARSET_COLL collation provides the collation that is closest to sorting on the client.
For Kanji1 character data, the ASCII collation provides a collation similar to that of the client, assuming the session character set is KanjiSJIS_0S, KanjiEUC_0U, or something very similar. However, ASCII collation does not sort like the client if the data is stored on the Teradata platform using the ShiftJIS or Unicode server character sets.
Note: Each character set requires a different definition for its MULTINATIONAL collation to collate properly.
Teradata Database handles character data collation for the different Japanese server character sets as follows:
ASCII collation collates Kanji1 data in binary order, but handles case matching of single‑byte or HALFWIDTH Latin characters (see International Character Set Support for details). This matches collation on the client for KanjiSJIS_0S sessions, is close for KanjiEUC_0U, and is only reasonably close for double‑byte data for KanjiEBCDIC sessions. Note that KanjiEUC_0U puts code set 3 after code set 1 rather than before code set 1 as KanjiEBCDIC does.
Note that it is not true that multibyte Kanji1 characters remain in the client encoding for KanjiEUC_0U sessions.
For details, see International Character Set Support and SQL Data Types and Literals.
International Sort Orders
MULTINATIONAL collation is enabled at the user level via the COLLATION option of the CREATE USER or MODIFY USER statement. If a collation is not specified, the default is HOST; that is, the standard sort ordering of the client system (EBCDIC for IBM clients, ASCII for all others).
You can override the default at the session level by issuing the SET SESSION COLLATION statement.
When MULTINATIONAL collation is in effect, the default collation sequence is determined by the collation setting installed at system start-up. Also see International Character Set Support.
Each international sort sequence is defined by program constants and no check is made to ensure that collation is compatible with the character set of the current session. The choice of sequence is controlled by your database administrator. The programmed character sequences cannot be changed.
European Sort Order
The sort order uses a two-level comparison that involves the following rules.
In ANSI session mode, the default is CASESPECIFIC and you must explicitly specify NOT CASESPECIFIC to change this.
For details, see SQL Data Types and Literals.
When these rules are applied, the words “abbey,” “Active,” and “adage” are returned in this order,
abbey Active adage
and the names Muller, Handl, Böckh, Mueller, Händl, Bohr, Bock, and Müller are ordered as:
Bock Böckh Bohr Handl
Händl Mueller Muller Müller
Equivalence classes and the ordering of diacritical characters in each class are shown in this table. The listed classes are those with characters that have diacritical forms.
European Sort Order |
|||||
A |
C |
E |
I |
N |
O |
a A à À á Á â Â ã Ã ä Ä |
c C ç Ç |
e E è È é É ê Ê ë Ë |
i I ì Ì í Í î Î ï Ï |
n N ñ Ñ |
o O ò Ò ó Ó ô Ô õ Õ ö Ö o O |
S |
U |
Y |
AE |
O slash |
A ring |
s S β |
u U ù Ù ú Ú û Û (U tilde) ü Ü |
y Y ÿ Ÿ |
æ Æ |
ø Ø |
å Å |
Example : Ordering on a Column Name
The following example produces a list of employees, sorted by years of work experience. Note that the ORDER BY sort column, yrs_exp, is also specified in the select list of the statement.
SELECT name, jobtitle, yrs_exp
FROM employee
ORDER BY yrsexp;
Example : Ordering on a Column Name Not Specified in the Select List
The following example produces a list of employees, sorted by dept_no and salary. Note that one of the ORDER BY sort columns, salary, is not specified in the select list.
SELECT name, dept_no
FROM employee
ORDER BY dept_no, salary;
Example : Ordering on Column Position
The following example substitutes the integer 3 for yrs_exp in the ORDER BY clause. The 3 refers to the left‑to‑right sequential numeric position of yrs_exp in the select expression list.
SELECT name, jobtitle, yrs_exp
FROM employee
ORDER BY 3;
Example : Ordering on a Column Name Alias
Define a column named j in table t1.
The following statement returns an error because j is defined to be an alias for the expression SUM(t1.j). However, when it is used in the ORDER BY clause, the system resolves it to the column j and not the expression aliased by j:
SELECT t1.i, SUM(t1.j) AS j, t1.k
FROM t1
GROUP BY 1,3
ORDER BY j;
The following statement works because the column name alias jj is not the same as the column name j, and there is no column named jj in the table definition.
SELECT t1.i, SUM(t1.j) AS jj, t1.k
FROM t1
GROUP BY 1,3
ORDER BY jj;
Example : Ordering on an Expression
The following example returns a list of each department number and its total population, in the order of lowest population first. The ORDER BY clause specifies a column expression, COUNT(name), to collate the response set.
SELECT COUNT(name), dept_no
FROM employee
GROUP BY dept_no,
ORDER BY COUNT(name);
Example : Ordering on Column Name or Column Position Using an Ascending or Descending Sort Sequence
Each of the following statements can be used to list employees by department number, with the highest paid employee listed first and the lowest paid last:
SELECT name, dept_no, salary
FROM employee
ORDER BY 2 ASC, 3 DESC;
SELECT name, dept_no, salary
FROM employee
ORDER BY dept_no, salary DESC;
Example : Effects of Default and User‑Specified Case Sensitivity on the Ordering of a Result Set
The following statements create and populate table t:
CREATE TABLE t (
a CHAR(4) NOT CASESPECIFIC,
b BYTEINT)
PRIMARY INDEX (a,b);
INSERT INTO t VALUES ('AAAA', 1);
INSERT INTO t VALUES ('aaaa', 2);
INSERT INTO t VALUES ('BBBB', 3);
INSERT INTO t VALUES ('bbbb', 4);
If the default handling of case is allowed, the following statement produces the following results table.
SELECT *
FROM t
ORDER BY a;
a b
‑‑‑‑ ---
AAAA 1
aaaa 2
BBBB 3
bbbb 4
On the other hand, when you specify CASESPECIFIC for the query, the results are one of the results tables immediately following the example SELECT statements, depending on the collation sequence in effect for the current session.
SELECT *
FROM t
ORDER BY CAST(a AS CASESPECIFIC);
or
SELECT CAST(a AS CASESPECIFIC), b
FROM t
ORDER BY 1;
EBCDIC |
ASCII |
MULTINATIONAL |
|||
A |
B |
A |
B |
A |
B |
---- |
---- |
---- |
---- |
---- |
---- |
aaaa |
2 |
AAAA |
1 |
aaaa |
2 |
bbbb |
4 |
BBBB |
3 |
AAAA |
1 |
AAAA |
1 |
aaaa |
2 |
bbbb |
4 |
BBBB |
3 |
bbbb |
4 |
BBBB |
3 |
Example : Ordering By a Column Alias Name
You cannot explicitly order by a column alias name. You must specify the column position of the column aliased by the name in the select list.
Suppose you have the following base table definition:
CREATE TABLE t1
(i INTEGER,
j INTEGER,
k INTEGER);
The following query against this table fails because it specifies ordering on the column alias name j, which the system interprets as column j in table t1 rather than as the expression SUM(t1.j):
SELECT t1.i, SUM(t1.j) AS j, t1.k
FROM t1
GROUP BY 1,3
ORDER BY j;
*** Failure 3504 Selected non-aggregate values must be part of the associated group.
The following rewrite of the query successfully performs the desired ordering:
SELECT t1.i, SUM(t1.j) AS j, t1.k
FROM t1
GROUP BY 1,3
ORDER BY 2;
Example : Ordering on a PERIOD Value Expression
The following example shows how you can specify a PERIOD value expression in an ORDER BY clause, where period_of_stay is the PERIOD value expression.
SELECT *
FROM employee
ORDER BY period_of_stay;
For More Information
For more information about the various ways you can specify character collation in Teradata Database, see: