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.

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;

Output:-

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;

Preparation of the Table

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

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