GROUP BY and ORDER BY Clauses - Advanced SQL Engine - Teradata Database

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
   );