RANK Function Examples (ANSI) | Teradata Vantage - 17.10 - Example: Ranking Salespeople Based on Sales - 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)

This example ranks salespersons by sales region based on their sales.

   SELECT sales_person, sales_region, sales_amount,
      RANK() OVER (PARTITION BY sales_region ORDER BY sales_amount DESC)
   FROM sales_table;
sales_person sales_region sales_amount Rank(sales_amount)
Garabaldi East 100 1
Baker East 99 2
Fine East 89 3
Adams East 75 4
Edwards West 100 1
Connors West 99 2
Davis West 99 2

The rank column in the preceding table lists salespersons in declining sales order according to the column specified in the PARTITION BY clause (sales_region) and that the rank of their sales (sales_amount) is reset when the sales_region changes.