17.10 - Computation Sort Order and Result Order - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

The sort order that you specify in the window specification defines the sort order of the rows over which the function is applied; it does not define the ordering of the results.

For example, to compute the average sales for the months following the current month, order the rows by month:

   SELECT StoreID, SMonth, ProdID, Sales,
   AVG(Sales) OVER (PARTITION BY StoreID ORDER BY SMonth
                    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
   FROM sales_tbl;
   
   StoreID  SMonth  ProdID      Sales  Remaining Avg(Sales)
   -------  ------  ------  ---------  --------------------
      1001       6  C        30000.00                     ?
      1001       5  C        30000.00              30000.00
      1001       4  C        25000.00              30000.00
      1001       3  C        40000.00              28333.33
      1001       2  C        25000.00              31250.00
      1001       1  C        35000.00              30000.00
   

The default sort order is ASC for the computation. However, the results are returned in the reverse order.

To order the results, use an ORDER BY phrase in the SELECT statement. For example:

   SELECT StoreID, SMonth, ProdID, Sales,
   AVG(Sales) OVER (PARTITION BY StoreID ORDER BY SMonth
                    ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
   FROM sales_tbl
   ORDER BY SMonth;
   
   StoreID  SMonth  ProdID      Sales  Remaining Avg(Sales)
   -------  ------  ------  ---------  --------------------
      1001       1  C        35000.00              30000.00
      1001       2  C        25000.00              31250.00
      1001       3  C        40000.00              28333.33
      1001       4  C        25000.00              30000.00
      1001       5  C        30000.00              30000.00
      1001       6  C        30000.00                     ?