The below example should help in understanding the QUALIFY statement in Teradata:
The table contains the following rows.
2 2
3 2
4 4
5 5
6 6
To Get Top 2nd row :
select * from tab7 QUALIFY RANK(col1 ASC) =2;
col1 col2———– ———–
To Get Top 4 rows :
select * from dup_tab1 QUALIFY RANK(col1 ASC) <= 4;
select TOP 10 * from dup_tab1;
select * from tab7 order by 1;
col1 col2
———– ———–1 2
2 2
3 2
4 4
5 5
6 6
Problem:
To Get 2nd row from the bottom.
select * from tab7 QUALIFY RANK(col1) =2;
col1 col2
5 5
To Get Top 2nd row :
select * from tab7 QUALIFY RANK(col1 ASC) =2;
col1 col2———– ———–
2 2
To Get Top 4 rows :
select * from dup_tab1 QUALIFY RANK(col1 ASC) <= 4;
To Get Top 4 rows randomly :
select TOP 10 * from dup_tab1;