Returns the nth smallest value from a list of inputs (e.g., ordinal(2, 5, 1, 3) returns 3, the 2nd smallest).
Syntax
ordinal(bigint <nth>, <var1>, <var2>[,...])Parameters
| Parameter | Type | Description |
|---|---|---|
nth |
BIGINT | The rank position to return, starting from 1. If null, null is returned. |
var |
BIGINT, DOUBLE, DATETIME, or STRING | The values to rank. |
Return value
The value at rank nth after sorting all inputs in ascending order.
Return type rules:
-
If all inputs share the same type, the return type matches that type.
-
If inputs mix DOUBLE, BIGINT, and STRING types, DOUBLE is returned.
-
If inputs mix STRING and DATETIME types, DATETIME is returned.
-
Implicit conversions between other type combinations are not supported.
Null handling: null is treated as the minimum value.
Examples
Basic usage
-- Returns 3 (the 3rd smallest value in: 1, 2, 3, 4, 5, 6, 7)
SELECT ordinal(3, 1, 3, 7, 5, 2, 4, 6);Null as minimum value
-- Returns null (null is the minimum, so nth=1 returns null)
SELECT ordinal(1, 5, null, 3);Related functions
该文章对您有帮助吗？