16.20 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
Language
English (United States)

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