Returns the original row key values (list of Ref
values), sorted as directed.
Sample usage
ORDERBY(Products[Product ID], [Product Name])
returns a list of Product ID
column values (the data set's key values) sorted by their corresponding Product Name
column values.
ORDERBY(Products[Product ID], [Product Price], TRUE, [Product Name])
sorts the product rows by price in descending (high-to-low/9-0/Z-A) order (per TRUE
). Rows with identical prices are further sorted by product name in the default, ascending (low-to-high/0-9/A-Z) order.
ORDERBY(SELECT(Customers[Customer ID], ([Country] = "Brazil")), [Customer Name])
sorts the IDs of customers in Brazil by customer name. Equivalent to ORDERBY(FILTER("Customers", ([Country] = "Brazil")), [Customer Name])
.See also: FILTER(),
SELECT()
Row with maximum value
INDEX(
ORDERBY(
FILTER(
"Students",
([Class of] = "2018")
),
[GPA],
TRUE
),
1
)
FILTER("Students", ([Class of] = "2018"))
gathers rows from theStudents
data set for the class of 2018.ORDERBY(..., [GPA], TRUE)
sorts the filtered rows by their correspondingGPA
column values in descending (high-to-low) order (perTRUE
).INDEX(..., 1)
extracts the first item (the row of the student with the highest GPA) from the sorted list.
Equivalent to MAXROW("Students", "GPA", ([Class of] = "2018"))
.
See also: FILTER()
, INDEX()
, MAXROW()
Syntax
ORDERBY(keys, sort-key, [descending-order?, [sort-key]]...)
-
keys
- List of key column values for the rows to be sorted as a list ofRef
values, commonly as generated withFILTER()
orSELECT()
. sort-key
- An expression that produces a sort key for the row. The sort key is compared against the corresponding sort keys of other rows to determine where the row will occur in the sorted list. The expression is evaluated in the context of the row being considered (similar to the match expressions inFILTER()
andSELECT()
). The simplest and most commonsort-key
expression is a reference to the column by which to sort (such as,[Product Name]
or[Start Date]
), but can be more complex.descending-order?
- AYes/No
expression. Set toFALSE
to sort by the immediate precedingsort-key
in ascending (low-to-high/0-9/A-Z) order, orTRUE
to sort in descending (high-to-low/9-0/Z-A) order.
Rows may be sorted by multiple sort keys; each additional sort key expression must be separated from the previous by an interveningdescending-order?
argument. The finaldescending-order?
argument is optional if its value would beFALSE
.
See also