Saturday, August 20, 2011

QUALIFY in Teradata

The below example should help in understanding the QUALIFY statement in Teradata:

The table contains the following rows.

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;

No comments:

Post a Comment

LinkWithin

top