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 about the various ways you can specify character collation in Teradata Database, see: