Qualify Clause in Teradata

The Qualify clause filters the results of a previously computed ordered analytical function according to user-specified search conditions.

The Qualify clause works similarly to the HAVING clause. But the major difference between Qualify and HAVING is that with Qualify the filtering is based on the result of performing various order analytical functions on the data.

Teradata Qualify Clause Syntax

The syntax of the Teradata Qaulify Clause is as below.

Qualify search_condition;

Teradata Qualify Clause Syntax Example

Consider the following performance table of Employee.

empid empname performance_marks
1 Austin 89
2 Robinson 84
3 Taylor 82
4 Mark 79
5 Peter 75

If you want to select the Employee details who secured the highest performance marks, you can use the Qualify clause along with the RANK() function as below.

SELECT * FROM Teradatapoint.Employee
QUALIFY RANK(performance_marks)=1;


empid Name Performance Marks
1 Austin 89

If you want to select the third-highest marks, you can get the desired result as below.

SELECT * FROM Teradatapoint.Employee
QUALIFY RANK(performance_marks)=3;

empid Name Performance Marks
3 Taylor 82

Preparation of the Table

CREATE TABLE Teradatapoint.Employee
empid INT,
empname varchar(50),
performance_marks INT

INSERT INTO Teradatapoint.Employee values (1,'Austin',89);
INSERT INTO Teradatapoint.Employee values(2,'Robinson',84);
INSERT INTO Teradatapoint.Employee values(3,'Taylor',82);
INSERT INTO Teradatapoint.Employee values(4,'Mark',79);
INSERT INTO Teradatapoint.Employee values(5,'Peter',75);