GROUP BY and ORDER BY Clauses - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

GROUP BY clauses are allowed in individual SELECT statements of a query expression but apply only to that SELECT statement and not to the result set.

ORDER BY clauses are allowed only in the last SELECT statement of a query expression and specify the order of the result set.

ORDER BY clauses can contain only numeric literals.

For example, to order by the first column in your result set, specify ORDER BY 1.

View definitions with set operators can use GROUP BY but cannot use ORDER BY. A SELECT statement applied to a view definition with set operators can use GROUP BY and ORDER BY. The following examples are correct uses of these operations within a view definition:

   REPLACE VIEW v AS 
   SELECT x1,y1 
   FROM table1 
   UNION
   SELECT x2,y2 
   FROM table2;
   
   SELECT * 
   FROM v 
   ORDER BY 1;
   
   SELECT SUM(x1), y1 
   FROM v 
   GROUP BY 2;

You can also apply independent GROUP BY operations to each unioned SELECT. The following example demonstrates how to do this:

   REPLACE VIEW v(column_1,column_2) AS
   SELECT MIN(x1),y1 
   FROM table_1 
   GROUP BY 2 
   UNION ALL
   SELECT MIN(x2),y2 
   FROM table_2 
   GROUP BY 2 
   UNION ALL 
   SELECT x3,y3 FROM table_3;
   
   SELECT SUM(v.column_1) (NAMED sum_c1),column_2
   GROUP BY 2 
   ORDER BY 2;
   
   SELECT * 
   FROM table_1
   WHERE (x1,y1) IN
   (SELECT SUM(x2), y2 
   FROM table_2
   GROUP BY 2
   UNION
   SELECT SUM(x3), y3 
   FROM table_3
   GROUP BY 2
   );