Example: Using QUALIFY With RANK

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

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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).