EXAMPLE - KTHLARGEST Function

Contents:

Expand all Collapse all

This example explores how you can use aggregation functions to calculate rank of values in a column.

Functions:

Item	Description
KTHLARGEST Function	Extracts the ranked value from the values in a column, where `k=1` returns the maximum value. The value for `k` must be between 1 and 1000, inclusive. Inputs can be Integer, Decimal, or Datetime.
KTHLARGESTUNIQUE Function	Extracts the ranked unique value from the values in a column, where `k=1` returns the maximum value. The value for `k` must be between 1 and 1000, inclusive. Inputs can be Integer, Decimal, or Datetime.

Source:

You have a set of student test scores:

Transformation:

You can use the following transformations to extract the 1st through 4th-ranked scores on the test:

Results:

When you reorganize the columns, the dataset might look like the following:

Student	Score	1st	2nd	3rd	4th	3rdUnique	4thUnique
Anna	84	99	92	87	87	87	85
Ben	71	99	92	87	87	87	85
Caleb	76	99	92	87	87	87	85
Danielle	87	99	92	87	87	87	85
Evan	85	99	92	87	87	87	85
Faith	92	99	92	87	87	87	85
Gabe	87	99	92	87	87	87	85
Hannah	99	99	92	87	87	87	85
Ian	73	99	92	87	87	87	85
Jane	68	99	92	87	87	87	85

Notes:

The value 87 is both the third and fourth scores.
- For the KTHLARGEST function, it is the output for the third and fourth ranking.
- For the KTHLARGESTUNIQUE function, it is the output for the third ranking only.

See Also for EXAMPLE - KTHLARGEST Function: