16.20 - Example - Teradata Database

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

prodname
Advanced SQL Engine
Teradata Database
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1145-162K

Sort items by category and report them in order of descending revenue rank.

   SELECT Category, Item, Revenue, RANK(Revenue) AS ItemRank
   FROM ItemCategory,
      (SELECT Item, SUM(sales) AS Revenue
      FROM DailySales
      GROUP BY Item) AS ItemSales
   WHERE ItemCategory.Item = ItemSales.Item
   ORDER BY Category, ItemRank DESC;

The result might look like the following table.

Category Item Revenue ItemRank
Hot Cereal Regular Oatmeal 39112.00 4
Hot Cereal Instant Oatmeal 44918.00 3
Hot Cereal Regular COW 59813.00 2
Hot Cereal Instant COW 75411.00 1