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.