RANK FUNCTION IN TERADATA

Share via:
RANK FUNCTION IN TERADATA

In this article you will get to know the RANK FUNCTION IN TERADATA

Purpose 

Returns the rank (1 … n) of all the rows in the group by the value of sort_expression list, with the same sort_expression values receiving the same rank.

Type

Teradata-specific function.

Syntax 
Sort_expression  :-    A literal or column expression or comma-separated list of literal or column expressions to be used to sort the values.

USE ELEWHERE:- For example, RANK(Region ASC, Store DESC), where Region ASC, Store DESC is the sort_expression list.

The expression cannot contain any ordered analytical or aggregate functions.

ASC -ascending sort order.

DSC -descending sort order.

The default sort direction is DESC

Meaning of Rank

A rank r implies the existence of exactly r-1 rows with sort_expression value preceding it. All rows having the same sort_expression value are assigned the same rank.

For example, if n rows have the same sort_expression values, then they are assigned the same rank, call it rank r. The next distinct value receives rank r+n.

Less formally, RANK sorts a result set and identifies the numeric rank of each row in the result. The only argument for RANK is the sort column or columns, and the function returns an integer that represents the rank of each row in the result.

Computing Top and Bottom Values

You can use RANK to compute top and bottom values as shown in the following examples.

Top(n, column) is computed as QUALIFY RANK(column DESC) <=n.

Bottom(n, column) is computed as QUALIFY RANK(column ASC) <=n

Examples 

QUALIFY With no Tied Values

There are only a few small items to notice here. First, there are no duplicate values and secondly, we are using the keyword RANK as an alias. When aliasing with a keyword, we must enclose the name with double-quotes. Thereafter, we must always refer to it by including the double-quotes. Lastly, we are using a QUALIFY to show the top three values.

Here we do not put a value into the rank function.

To be more precise, we are not allowed to place anything into the function (i.e., RANK(sales) is not allowed with the window aggregate version of rank).

Instead, what determines the ranking is the ORDER BY used in the OVER portion of the syntax. The default for RANK is ASC.

Show the top 3 selling items for “2008-05-24”

QUALIFY With Tied Ending Values

In the example on the facing page note that there are 6 rows returned even thought there are only 5 different ranking values (as requested). You may also have noted that there are two sets of tied values. Note how the result set skipped from a rank value of “1” to a ranked value of “3”. The next rank value after “5” will be 7.. Another value of “5”, in the result (bringing the number of “5”s to 3) would raise the next ranking value from “7” to “8”.

Qualifying Without Rank Projection

Bottom Values by ASC Rank

Show the bottom 3 selling items across all dates and all items

RANK and PARTITION

Show the top 3 selling items for each day for a 3-day period, withoutshowing their rank value

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Add Comment