Sorting

There are multiple ORDER variants available: Ascending - ORDER, ORDERSTRICT - ORDERINDEX, ORDERINDEXSTRICT Descending - ORDERDESC, ORDERDESCSTRICT - ORDERINDEXDESC, ORDERINDEXDESCSTRICT

ORDER#

The ORDER function sorts the provided input values in ascending order and returns them in a new collection. It accepts n values (up to 100 arguments) or a single collection. Values do not need to be numeric, but they must be comparable (e.g., numbers, TIMESPAN, DATETIME, strings). Mixed types (e.g., numbers and strings) are not allowed. NaN and NULL values are ignored and excluded from the returned collection.

ORDER( n1, n2, n3 , … )
ORDER( collection )

Examples:

ORDER(3, 1, 2)				= {1, 2, 3}
ORDER(1)					= {1}
ORDER(3, NaN, 2, NaN)		= {2, 3}
ORDER(NaN)					= {}
ORDER('Z', 'a', 'X')				= {'X', 'Z', 'a'}
ORDER(“str1”, “STR2”, “stR1”} 		= {"STR2", "stR1", "str1"}
ORDER(“str1”, NULL, “STR2”, “stR1”} = {"STR2", "stR1", "str1"}

ORDER(TIMESPAN(0,0,0,0,100), TIMESPAN(0,0,0,0,99)	= {TIMESPAN(0,0,0,0,99), TIMESPAN(0,0,0,0,100)}
ORDER(NULL, TIMESPAN(0,0,0,0,99)					= {TIMESPAN(0,0,0,0,99)}

ORDERINDEX#

The ORDERINDEX function sorts the provided input values in ascending order, similar to the ORDER function. However, instead of returning the sorted values, it returns a collection of indexes indicating the positions of the original values in sorted order. It accepts n values (up to 100 arguments) or a single collection. Values do not need to be numeric, but they must be comparable (e.g., numbers, TIMESPAN, DATETIME, strings). Mixed types (e.g., numbers and strings) are not allowed. NaN and NULL values are ignored and excluded from the returned collection.

ORDERINDEX( n1, n2, n3 , … )
ORDERINDEX( collection )

Examples:

ORDERINDEX(3, 1, 2)				= {1, 2, 0}
ORDERINDEX(1)					= {0}
ORDERINDEX(3, NaN, 2, NaN)		= {2, 0}
ORDERINDEX(NaN)					= {}
ORDERINDEX('Z', 'a', 'X')				= {2, 0, 1}
ORDERINDEX(“str1”, “STR2”, “stR1”} 		= {1, 2, 0}
ORDERINDEX(“str1”, NULL, “STR2”, “stR1”} = {2, 3, 0}

ORDERINDEX(TIMESPAN(0,0,0,0,100), TIMESPAN(0,0,0,0,99)	= {1, 0}
ORDERINDEX(NULL, TIMESPAN(0,0,0,0,99)					= {1}

ORDERSTRICT#

The ORDERSTRICT function sorts the provided input values in ascending order and returns them in a new collection. It accepts n values (up to 100 arguments) or a single collection. Values do not need to be numeric, but they must be comparable (e.g., numbers, TIMESPAN, DATETIME, strings). Mixed types (e.g., numbers and strings) are not allowed. NaN and NULL values are included and always sorted first in the result.

ORDERSTRICT( n1, n2, n3 , … )
ORDERSTRICT( collection )

Examples:

ORDERSTRICT(3, 1, 2)			= {1, 2, 3}
ORDERSTRICT(1)					= {1}
ORDERSTRICT(3, NaN, 2, NaN)		= {NaN, NaN, 2, 3}
ORDERSTRICT(NaN)				= {NaN}
ORDERSTRICT('Z', 'a', 'X')		= {'X', 'Z', 'a'}
ORDERSTRICT(“str1”, “STR2”, “stR1”} 		= {"STR2", "stR1", "str1"}
ORDERSTRICT(“str1”, NULL, “STR2”, “stR1”}	= {NULL, "STR2", "stR1", "str1"}

ORDERSTRICT(TIMESPAN(0,0,0,0,100), TIMESPAN(0,0,0,0,99)	= {TIMESPAN(0,0,0,0,99), TIMESPAN(0,0,0,0,100)}
ORDERSTRICT(NULL, TIMESPAN(0,0,0,0,99)					= {NULL, TIMESPAN(0,0,0,0,99)}

ORDERINDEXSTRICT#

The ORDERINDEXSTRICT function sorts the provided input values in ascending order, similar to the ORDERSTRICT function. However, instead of returning the sorted values, it returns a collection of indexes indicating the positions of the original values in sorted order. It accepts n values (up to 100 arguments) or a single collection. Values do not need to be numeric, but they must be comparable (e.g., numbers, TIMESPAN, DATETIME, strings). Mixed types (e.g., numbers and strings) are not allowed. NaN and NULL values are included and always sorted first in the result.

ORDERINDEXSTRICT( n1, n2, n3 , … )
ORDERINDEXSTRICT( collection )

Examples:

ORDERINDEXSTRICT(3, 1, 2)				= {1, 2, 0}
ORDERINDEXSTRICT(1)						= {0}
ORDERINDEXSTRICT(3, NaN, 2, NaN)		= {1, 3, 2, 0}
ORDERINDEXSTRICT(NaN)					= {0}
ORDERINDEXSTRICT('Z', 'a', 'X')			= {2, 0, 1}
ORDERINDEXSTRICT(“str1”, “STR2”, “stR1”} 			= {1, 2, 0}
ORDERINDEXSTRICT(“str1”, NULL, “STR2”, “stR1”} 		= {1, 2, 3, 0}

ORDERINDEXSTRICT(TIMESPAN(0,0,0,0,100), TIMESPAN(0,0,0,0,99)	= {1, 0}
ORDERINDEXSTRICT(NULL, TIMESPAN(0,0,0,0,99)						= {0, 1}

ORDERDESC#

The ORDERDESC function sorts the provided input values in descending order and returns them in a new collection. It accepts n values (up to 100 arguments) or a single collection. Values do not need to be numeric, but they must be comparable (e.g., numbers, TIMESPAN, DATETIME, strings). Mixed types (e.g., numbers and strings) are not allowed. NaN and NULL values are ignored and excluded from the returned collection.

ORDERDESC( n1, n2, n3 , … )
ORDERDESC( collection )

Examples:

ORDERDESC(3, 1, 2)					= {3, 2, 1}
ORDERDESC(1)						= {1}
ORDERDESC(3, NaN, 2, NaN)			= {3, 2}
ORDERDESC(NaN)						= {}
ORDERDESC('Z', 'a', 'X')			= {'a', 'Z', 'X'}
ORDERDESC(“str1”, “STR2”, “stR1”} 	= {"str1", "stR1", "STR2"}
ORDERDESC(“str1”, NULL, “STR2”, “stR1”} 	= {"str1", "stR1", "STR2"}

ORDERDESC(TIMESPAN(0,0,0,0,100), TIMESPAN(0,0,0,0,99)	= {TIMESPAN(0,0,0,0,100), TIMESPAN(0,0,0,0,99)}
ORDERDESC(NULL, TIMESPAN(0,0,0,0,99)					= {TIMESPAN(0,0,0,0,99)}

ORDERINDEXDESC#

The ORDERINDEXDESC function sorts the provided input values in descending order, similar to the ORDERDESC function. However, instead of returning the sorted values, it returns a collection of indexes indicating the positions of the original values in sorted order. It accepts n values (up to 100 arguments) or a single collection. Values do not need to be numeric, but they must be comparable (e.g., numbers, TIMESPAN, DATETIME, strings). Mixed types (e.g., numbers and strings) are not allowed. NaN and NULL values are ignored and excluded from the returned collection.

ORDERINDEXDESC( n1, n2, n3 , … )
ORDERINDEXDESC( collection )

Examples:

ORDERINDEXDESC(3, 1, 2)				= {0, 2, 1}
ORDERINDEXDESC(1)					= {0}
ORDERINDEXDESC(3, NaN, 2)			= {0, 2}
ORDERINDEXDESC(NaN)					= {}
ORDERINDEXDESC('Z', 'a', 'X')					= {1, 0, 2}
ORDERINDEXDESC(“str1”, “STR2”, “stR1”} 			= {0, 2, 1}
ORDERINDEXDESC(“str1”, NULL, “STR2”, “stR1”} 	= {0, 3, 2}

ORDERINDEXDESC(TIMESPAN(0,0,0,0,100), TIMESPAN(0,0,0,0,99)	= {0, 1}
ORDERINDEXDESC(NULL, TIMESPAN(0,0,0,0,99)					= {1}

ORDERDESCSTRICT#

The ORDERDESCSTRICT function sorts the provided input values in descending order and returns them in a new collection. It accepts n values (up to 100 arguments) or a single collection. Values do not need to be numeric, but they must be comparable (e.g., numbers, TIMESPAN, DATETIME, strings). Mixed types (e.g., numbers and strings) are not allowed. NaN and NULL values are included and always sorted first in the result.

ORDERDESCSTRICT( n1, n2, n3 , … )
ORDERDESCSTRICT( collection )

Examples:

ORDERDESCSTRICT(3, 1, 2)			= {3, 2, 1}
ORDERDESCSTRICT(1)					= {1}
ORDERDESCSTRICT(3, NaN, 2, NaN		= {NaN, NaN, 3, 2}
ORDERDESCSTRICT(NaN)				= {NaN}
ORDERDESCSTRICT('Z', 'a', 'X')					= {'a', 'Z', 'X'}
ORDERDESCSTRICT(“str1”, “STR2”, “stR1”} 		= {"str1", "stR1", "STR2"}
ORDERDESCSTRICT(“str1”, NULL, “STR2”, “stR1”} 	= {NULL, "str1", "stR1", "STR2"}

ORDERDESCSTRICT(TIMESPAN(0,0,0,0,100), TIMESPAN(0,0,0,0,99)	= {TIMESPAN(0,0,0,0,100), TIMESPAN(0,0,0,0,99)}
ORDERDESCSTRICT(NULL, TIMESPAN(0,0,0,0,99)					= {NULL, TIMESPAN(0,0,0,0,99)}

ORDERINDEXDESCSTRICT#

The ORDERINDEXDESCSTRICT function sorts the provided input values in descending order, similar to the ORDERDESC function. However, instead of returning the sorted values, it returns a collection of indexes indicating the positions of the original values in sorted order. It accepts n values (up to 100 arguments) or a single collection. Values do not need to be numeric, but they must be comparable (e.g., numbers, TIMESPAN, DATETIME, strings). Mixed types (e.g., numbers and strings) are not allowed. NaN and NULL values are included and always sorted first in the result.

ORDERINDEXDESCSTRICT( n1, n2, n3 , … )
ORDERINDEXDESCSTRICT( collection )

Examples:

ORDERINDEXDESCSTRICT(3, 1, 2)				= {0, 2, 1}
ORDERINDEXDESCSTRICT(1)						= {0}
ORDERINDEXDESCSTRICT(3, NaN, 2, NaN)		= {1, 3, 0, 2}
ORDERINDEXDESCSTRICT(NaN)					= {0}
ORDERINDEXDESCSTRICT('Z', 'a', 'X')			= {1, 0, 2}
ORDERINDEXDESCSTRICT(“str1”, “STR2”, “stR1”}		= {0, 2, 1}
ORDERINDEXDESCSTRICT(“str1”, NULL, “STR2”, “stR1”} 	= {1, 0, 3, 2}

ORDERINDEXDESCSTRICT(TIMESPAN(0,0,0,0,100), TIMESPAN(0,0,0,0,99)	= {0, 1}
ORDERINDEXDESCSTRICT(NULL, TIMESPAN(0,0,0,0,99)						= {0, 1}