Teradata ORDER BY clause sorts the result set based on the given expression either in ascending or descending order. The default is ascending order. The order by clause is used with the select statement. The select statement selects the rows and columns as per the given columns and the expression. If the order by clause is not used the selected rows will remain unsorted.
Teradata ORDER BY syntax
The generic syntax of the Teradata ORDER BY clause is as follows.
SELECT column1, column2, ......,columnN FROM DatabaseName.TableName ORDER BY expression [ASC|DESC] [ NULL First | NULL Last ];
Parameters Explanation
| Parameter | Explanation |
| expression as column_name | The name of the column by which you want to sort the result set. |
| expression as column_name_alias | The alias name of the column by which you want to sort the result-set. |
| expression as column_position | You can specify the position of the column in the select list. |
| ASC|DESC | The result set can be ordered in ascending order or descending order. |
| NULL First | Null results are to be listed first. |
| NULL Last | Null results are to be listed last. |
Teradata ORDER BY example
Let’s understand the concept of the Teradata ORDER BY clause with the help of some examples.
Suppose, we have an Employee table stored in the Teradata database.
If we run the select query on the table, the result may look as below.
SELECT
EMPNO,
FIRST_NAME,
LAST_NAME,
DESIGNATION
FROM TUTORIALSBOOK.Employee;
*** Query completed. 8 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
EMPNO FIRST_NAME LAST_NAME DESIGNATION
----------- ------------------------- ------------------------- --------------------
1008 JOHN SMITH SALESMAN
1004 JONES NICK HR ANALYST
1006 ISSAC STEFAN ASSISTANT MANAGER
1002 DIANA LORANCE TECHNICAL ARCHITECT
1007 NANCY GILBERT CLERK
1003 JAMES MADISON MANAGER
1005 LUCY GILLER HR ASSOCIATE
1001 STEFAN SALVATORE ASSISTANT MANAGER
From the output, you can notice that the result set is unsorted.
1) Sort the result set based on the column name
If you want to sort the result set on the EMPNO column, you can use the following statement.
SELECT EMPNO, FIRST_NAME, LAST_NAME, DESIGNATION FROM TUTORIALSBOOK.Employee ORDER BY EMPNO;
Output:
*** Query completed. 8 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
EMPNO FIRST_NAME LAST_NAME DESIGNATION
----------- ------------------------- ------------------------- --------------------
1001 STEFAN SALVATORE ASSISTANT MANAGER
1002 DIANA LORANCE TECHNICAL ARCHITECT
1003 JAMES MADISON MANAGER
1004 JONES NICK HR ANALYST
1005 LUCY GILLER HR ASSOCIATE
1006 ISSAC STEFAN ASSISTANT MANAGER
1007 NANCY GILBERT CLERK
1008 JOHN SMITH SALESMAN
2) Sort the result set based on the column alias
You can sort the result set based on the column alias below.
SELECT EMPNO AS EMPID, FIRST_NAME, LAST_NAME, DESIGNATION FROM TUTORIALSBOOK.Employee ORDER BY EMPID;
Output:
*** Query completed. 8 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
EMPID FIRST_NAME LAST_NAME DESIGNATION
----------- ------------------------- ------------------------- --------------------
1001 STEFAN SALVATORE ASSISTANT MANAGER
1002 DIANA LORANCE TECHNICAL ARCHITECT
1003 JAMES MADISON MANAGER
1004 JONES NICK HR ANALYST
1005 LUCY GILLER HR ASSOCIATE
1006 ISSAC STEFAN ASSISTANT MANAGER
1007 NANCY GILBERT CLERK
1008 JOHN SMITH SALESMAN
3) Sort the result set based on the column position
For sorting the result set column position also can be used in place of the column name.
SELECT EMPNO, FIRST_NAME, LAST_NAME, DESIGNATION FROM TUTORIALSBOOK.Employee ORDER BY 1;
Output:
*** Query completed. 8 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
EMPNO FIRST_NAME LAST_NAME DESIGNATION
----------- ------------------------- ------------------------- --------------------
1001 STEFAN SALVATORE ASSISTANT MANAGER
1002 DIANA LORANCE TECHNICAL ARCHITECT
1003 JAMES MADISON MANAGER
1004 JONES NICK HR ANALYST
1005 LUCY GILLER HR ASSOCIATE
1006 ISSAC STEFAN ASSISTANT MANAGER
1007 NANCY GILBERT CLERK
1008 JOHN SMITH SALESMAN
4) Sort the result set in descending order
You can sort the result set in descending order as well using the DESC keyword.
SELECT EMPNO, FIRST_NAME, LAST_NAME, DESIGNATION FROM TUTORIALSBOOK.Employee ORDER BY 1 DESC;
Output:
*** Query completed. 8 rows found. 4 columns returned.
*** Total elapsed time was 1 second.
EMPNO FIRST_NAME LAST_NAME DESIGNATION
----------- ------------------------- ------------------------- --------------------
1008 JOHN SMITH SALESMAN
1007 NANCY GILBERT CLERK
1006 ISSAC STEFAN ASSISTANT MANAGER
1005 LUCY GILLER HR ASSOCIATE
1004 JONES NICK HR ANALYST
1003 JAMES MADISON MANAGER
1002 DIANA LORANCE TECHNICAL ARCHITECT
1001 STEFAN SALVATORE ASSISTANT MANAGER