View Source

This example explores how you can use aggregation functions to calculate rank of values in a column using the KTHLARGEST and KTHLARGESTUNIQUE functions.

See KTHLARGEST Function.
See KTHLARGESTUNIQUE Function.

Source:

You have a set of student test scores:

Student	Score
Anna	84
Ben	71
Caleb	76
Danielle	87
Evan	85
Faith	92
Gabe	87
Hannah	99
Ian	73
Jane	68

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.