You are viewing content for 5.1 | 5.0 | 4.9 | 4.8 | 4.7 | 4.6 | 4.5 | 4.4 | 4.3 | Previous Releases


Built-in Functions → Analytic Functions

An analytic function calculates an aggregate value based on a group of rows and returns a single value for each row.

Function Signature Description
rank() rank(groupBy, orderBy) Returns the rank of a value based on its order in the grouped values. Multiple rows can share the same rank resulting in nonconsecutive ranks.
denseRank() denseRank(groupBy, orderBy) Returns a consecutive rank of each row based on the order of the grouped values
index() index(groupBy, orderBy) Returns the index of rows based on the order of the grouped values

The following table illustrates the behavior of the Analytic functions:

Category Product Min List Price rank() denseRank() index()
Fruit Apple 0.25 1 1 1
Fruit Orange 0.35 2 2 2
Fruit Banana 0.40 3 3 3
Fruit Lemon 0.40 3 3 4
Fruit Kiwi 0.75 5 4 5
Fruit Plum 0.75 5 4 6
Fruit Apricot 0.75 5 4 7
Fruit Yellow Melon 3.50 8 5 8
Fruit Cantaloupe 3.50 8 5 9
Fruit Pineapple 5.00 10 6 10
© Incorta, Inc. All Rights Reserved.