Sorting

The ORDER functions sort values and return a new collection — either the sorted values or, in the ORDERINDEX variants, the original zero-based positions of the values in sorted order.

Rules shared by all eight functions:

• They take 1 to 100 values, or exactly one collection. The source collection is not changed.
• The values must be comparable and of one kind — numbers, characters, strings, datetime or timespan; mixing numbers and strings is an error.
• Strings and characters are sorted by character code, so uppercase letters come before lowercase ones.
• Equal values keep their original order.
• The STRICT variants place unknown values first, in the descending variants too.
• An empty collection is an error: Collection is null or empty.

Example — the cheapest hour from a list of prices

VAR prices := {12.5, 8.1, 20.0, 9.9};
VAR cheapest := GETAT(ORDER(prices), 0);
# cheapest = 8.1
VAR cheapestHour := GETAT(ORDERINDEX(prices), 0);
# cheapestHour = 1

ORDER#

ORDER returns the values sorted in ascending order, without Null and NaN.

1
2

ORDER(value1, value2, ...)
ORDER(collection)

# numbers, ascending
ORDER(3, 1, 2);
# Result: {1, 2, 3}
# NaN values are removed
ORDER(3, NaN, 2, NaN);
# Result: {2, 3}
# characters by character code
ORDER('Z', 'a', 'X');
# Result: {'X', 'Z', 'a'}
# strings by character code, NULL removed
ORDER("str1", NULL, "STR2", "stR1");
# Result: {"STR2", "stR1", "str1"}
# timespans, NULL removed
ORDER(NULL, TIMESPAN(0, 0, 0, 0, 100), TIMESPAN(0, 0, 0, 0, 99));
# Result: {TIMESPAN(0, 0, 0, 0, 99), TIMESPAN(0, 0, 0, 0, 100)}

ORDERSTRICT#

ORDERSTRICT returns the values sorted in ascending order and keeps Null and NaN at the beginning.

1
2

ORDERSTRICT(value1, value2, ...)
ORDERSTRICT(collection)

# NaN values are kept first
ORDERSTRICT(3, NaN, 2, NaN);
# Result: {NaN, NaN, 2, 3}
# strings by character code
ORDERSTRICT("str1", "STR2", "stR1");
# Result: {"STR2", "stR1", "str1"}
# NULL is kept first
ORDERSTRICT(NULL, TIMESPAN(0, 0, 0, 0, 99));
# Result: {NULL, TIMESPAN(0, 0, 0, 0, 99)}

ORDERDESC#

ORDERDESC returns the values sorted in descending order, without Null and NaN.

1
2

ORDERDESC(value1, value2, ...)
ORDERDESC(collection)

# numbers, descending
ORDERDESC(3, 1, 2);
# Result: {3, 2, 1}
# NaN values are removed
ORDERDESC(3, NaN, 2, NaN);
# Result: {3, 2}
# characters, descending by code
ORDERDESC('Z', 'a', 'X');
# Result: {'a', 'Z', 'X'}
# strings, descending, NULL removed
ORDERDESC("str1", NULL, "STR2", "stR1");
# Result: {"str1", "stR1", "STR2"}

ORDERDESCSTRICT#

ORDERDESCSTRICT returns the values sorted in descending order and keeps Null and NaN at the beginning.

1
2

ORDERDESCSTRICT(value1, value2, ...)
ORDERDESCSTRICT(collection)

ORDERDESCSTRICT(3, NaN, 2, NaN);
# Result: {NaN, NaN, 3, 2}
ORDERDESCSTRICT(NULL, TIMESPAN(0, 0, 0, 0, 99), TIMESPAN(0, 0, 0, 0, 100));
# Result: {NULL, TIMESPAN(0, 0, 0, 0, 100), TIMESPAN(0, 0, 0, 0, 99)}

ORDERINDEX#

ORDERINDEX returns the positions of the values in ascending order of value, without the positions of Null and NaN.

1
2

ORDERINDEX(value1, value2, ...)
ORDERINDEX(collection)

Returns — collection<int> of zero-based positions in the input. The first element is the position of the smallest value.

# positions of numbers, ascending
ORDERINDEX(3, 1, 2);
# Result: {1, 2, 0}
# positions of NaN are left out
ORDERINDEX(3, NaN, 2, NaN);
# Result: {2, 0}
# positions of strings, NULL left out
ORDERINDEX("str1", NULL, "STR2", "stR1");
# Result: {2, 3, 0}

ORDERINDEXSTRICT#

ORDERINDEXSTRICT returns the positions of the values in ascending order of value, with the positions of Null and NaN first.

1
2

ORDERINDEXSTRICT(value1, value2, ...)
ORDERINDEXSTRICT(collection)

# positions of numbers, ascending
ORDERINDEXSTRICT(3, 1, 2);
# Result: {1, 2, 0}
# positions of NaN come first
ORDERINDEXSTRICT(3, NaN, 2, NaN);
# Result: {1, 3, 2, 0}
# position of NULL comes first
ORDERINDEXSTRICT(NULL, TIMESPAN(0, 0, 0, 0, 99));
# Result: {0, 1}

ORDERINDEXDESC#

ORDERINDEXDESC returns the positions of the values in descending order of value, without the positions of Null and NaN.

1
2

ORDERINDEXDESC(value1, value2, ...)
ORDERINDEXDESC(collection)

# positions of numbers, descending
ORDERINDEXDESC(3, 1, 2);
# Result: {0, 2, 1}
# positions of NaN are left out
ORDERINDEXDESC(3, NaN, 2);
# Result: {0, 2}
# positions of strings, NULL left out
ORDERINDEXDESC("str1", NULL, "STR2", "stR1");
# Result: {0, 3, 2}

ORDERINDEXDESCSTRICT#

ORDERINDEXDESCSTRICT returns the positions of the values in descending order of value, with the positions of Null and NaN first.

1
2

ORDERINDEXDESCSTRICT(value1, value2, ...)
ORDERINDEXDESCSTRICT(collection)

# positions of numbers, descending
ORDERINDEXDESCSTRICT(3, 1, 2);
# Result: {0, 2, 1}
# positions of NaN come first
ORDERINDEXDESCSTRICT(3, NaN, 2, NaN);
# Result: {1, 3, 0, 2}
# position of NULL comes first
ORDERINDEXDESCSTRICT(NULL, TIMESPAN(0, 0, 0, 0, 99));
# Result: {0, 1}

See also — Text and collections, Mathematical functions.