Example: Using QUALIFY With RANK - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantageā„¢

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