ORDINAL

Command format

ORDINAL(BIGINT <nth>, <var1>, <var2>[,...])

-- Standard example.
-- Returns 3.
SELECT ordinal(3L, 1L, 3L, 7L, 5L, 2L, 4L, 6L); 

Parameter descriptions

• nth: Required. Specifies the 1-based position of the value to return. The data type is BIGINT. If this parameter is set to NULL, the function returns NULL.

• var: Required. The values to sort. The data type can be BIGINT, DOUBLE, DATETIME, or STRING.

Return value description

• The value at the nth position. If no implicit conversion occurs, the return value has the same data type as the input parameters.

• If a data type conversion occurs, conversions among DOUBLE, BIGINT, and STRING types return a DOUBLE value. Conversions between STRING and DATETIME types return a DATETIME value. No other implicit conversions are allowed.

• NULL is treated as the minimum value.

Usage example

-- Returns 3.
SELECT ordinal(3L, 1L, 3L, 7L, 5L, 2L, 4L, 6L); 

Related functions

The ORDINAL function is a miscellaneous function. For more functions for other business scenarios, see Other functions.