Computation Sort Order and Result Order

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

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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                     ?