ORDERBY()

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
)

1. FILTER("Students", ([Class of] = "2018")) gathers rows from the Students data set for the class of 2018.
2. ORDERBY(..., [GPA], TRUE) sorts the filtered rows by their corresponding GPA column values in descending (high-to-low) order (per TRUE).
3. 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 of Ref values, commonly as generated with FILTER() or SELECT() .

• 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 in FILTER() and SELECT() ). The simplest and most common sort-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 - A Yes/No expression. Set to FALSE to sort by the immediate preceding sort-key in ascending (low-to-high/0-9/A-Z) order, or TRUE 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 intervening descending-order argument. The final descending-order argument is optional if its value would be FALSE.

See also

FILTER()

MAXROW()

MINROW()

REF_ROWS()

SORT()