Ranking and percentile functions

Place one value among many.

Syntax

Write the function's name with the value or the list first, as each entry below shows. The list is written in square brackets or held in a variable.

RANK(number, ref, [order])
RANK.EQ(number, ref, [order])
RANK.AVG(number, ref, [order])
PERCENTRANK(array, x, [significance])
PERCENTRANK.INC(array, x, [significance])
PERCENTRANK.EXC(array, x, [significance])
PERCENTILE.EXC(array, k)
QUARTILE(array, quart)
QUARTILE.EXC(array, quart)

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • RANK

    number

    Required: Yes
  • ref

    Required: Yes
  • order

    Required: No
  • RANK.EQ

    number

    Required: Yes
  • ref

    Required: Yes
  • order

    Required: No
  • RANK.AVG

    number

    Required: Yes
  • ref

    Required: Yes
  • order

    Required: No
  • PERCENTRANK

    array

    Required: Yes
  • x

    Required: Yes
  • significance

    Required: No
  • PERCENTRANK.INC

    array

    Required: Yes
  • x

    Required: Yes
  • significance

    Required: No
  • PERCENTRANK.EXC

    array

    Required: Yes
  • x

    Required: Yes
  • significance

    Required: No
  • PERCENTILE.EXC

    array

    Required: Yes
  • k

    Required: Yes
  • QUARTILE

    array

    Required: Yes
  • quart

    Required: Yes
  • QUARTILE.EXC

    array

    Required: Yes
  • quart

    Required: Yes

Examples

Five stores' weekly sales held once: where a store of 515 stands from the top, from the bottom, and how a tie is shared.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
RANKwhole numberThe place of the value in the list. Equal values share the placeRANK(5, [1, 5, 9])
RANK.EQwhole numberThe same as RANKRANK.EQ(5, [1, 5, 9])
RANK.AVGdecimalThe place of the value, where equal values share the average of the places they coverRANK.AVG(5, [1, 5, 5, 9])
PERCENTRANKdecimalThe share of the list below the value, from 0 to 1PERCENTRANK([1, 2, 3, 4, 5], 4)
PERCENTRANK.INCdecimalThe same as PERCENTRANKPERCENTRANK.INC([1, 2, 3, 4, 5], 4)
PERCENTRANK.EXCdecimalThe share of the list below the value, counted as if one more value sat at each end, so never 0 or 1PERCENTRANK.EXC([1, 2, 3, 4, 5], 4)
PERCENTILE.EXCdecimalThe value at that fraction of the list, counted as if one more value sat at each endPERCENTILE.EXC([1, 2, 3, 4, 5], 0.5)
QUARTILEdecimalThe quarter point of the list, counting the ends as included. The same as QUARTILE.INCQUARTILE([1, 2, 3, 4, 5], 3)
QUARTILE.EXCdecimalThe quarter point of the list, counted as if one more value sat at each endQUARTILE.EXC([1, 2, 3, 4, 5], 1)
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-2001An argument is not a number or a list of numbers, a value is missing from the list for RANK, or a value or quarter is out of rangePass numbers in range, and a value that is in the list
HTQL-8004PERCENTILE.EXC was given a fraction not above 0 and below 1, or a list too short to place it; QUARTILE.EXC was given a list too shortPass a fraction strictly between 0 and 1 and enough values
Limitations
  • RANK, RANK.EQ and RANK.AVG refuse a value that is not in the list.
  • PERCENTRANK and PERCENTRANK.INC refuse a value outside the range of the list; PERCENTRANK.EXC refuses a value that is not strictly inside it.
  • QUARTILE needs a quarter from 0 to 4 and QUARTILE.EXC from 1 to 3; QUARTILE.EXC needs at least 4 values for quarters 1 and 3. PERCENTILE.EXC needs a fraction strictly between 0 and 1.
  • The list holds numbers only; an empty value or text in it is refused.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Use RANK.AVG when ties should not look like a clear win.
  • Use QUARTILE or PERCENTILE.EXC for a service promise such as "nine in ten orders arrive within".
  • Use PERCENTRANK to show a customer or a store where it stands, as a share of its peers.