Consider the following sales table named sales_tbl.
| Store | ProdID | Sales |
|---|---|---|
| 1003 | C | 20000.00 |
| 1003 | D | 50000.00 |
| 1003 | A | 30000.00 |
| 1002 | C | 35000.00 |
| 1002 | D | 25000.00 |
| 1002 | A | 40000.00 |
| 1001 | C | 60000.00 |
| 1001 | D | 35000.00 |
| 1001 | A | 100000.00 |
| 1001 | B | 10000.00 |
Now perform the following simple SELECT statement against this table, qualifying answer rows by rank.
SELECT store, prodID, sales, RANK() OVER (PARTITION BY store ORDER BY sales DESC) FROM sales_tbl QUALIFY RANK() OVER (PARTITION BY store ORDER BY sales DESC) <=3;
The result appears in the following typical output table.
| Store | ProdID | Sales | Rank(Sales) |
|---|---|---|---|
| 1001 | A | 100000.00 | 1 |
| 1001 | C | 60000.00 | 2 |
| 1001 | D | 35000.00 | 3 |
| 1002 | A | 40000.00 | 1 |
| 1002 | C | 35000.00 | 2 |
| 1002 | D | 25000.00 | 3 |
| 1003 | D | 50000.00 | 1 |
| 1003 | A | 30000.00 | 2 |
| 1003 | C | 20000.00 | 3 |
Note that every row in the table is returned with the computed value for RANK except those that do not meet the QUALIFY clause (sales rank is less than third within the store).