Learning
Community
Open Exchange
Global Masters
InterSystems IRIS Data Platform 2019.3 / Analytics / InterSystems MDX Reference / MDX Functions / ORDER
Previous section   Next section

ORDER

Returns a set that is ordered as specified.

Returned Type

This function returns a set.

Syntax and Details

ORDER(set_expression, ordering_expression, optional_keyword)
Where:
  • set_expression is an expression that evaluates to a set, typically a set of members.
  • ordering_expression is a numeric expression or a string expression that determines the order of the set elements.
    For numeric values, typically ordering_expression is [MEASURES].[measure_name]
    The function evaluates this expression for each element of the set and sorts the elements of the set according to this value.
  • optional_keyword controls how MDX handles any hierarchies in the set. Use one of the following keywords:
    • ASC — Use this to sort in ascending order (using the value returned by ordering_expression), while preserving the hierarchy. For information on hierarchical order, see HIERARCHIZE.
      If you omit the keyword, the function sorts in this way.
    • DESC — Use this to sort in descending order (using the value returned by ordering_expression), while preserving the hierarchy.
    • BASC — Use this to break the hierarchy and sort all members in ascending order (using the value returned by ordering_expression).
    • BDESC — Use this to break the hierarchy and sort all members in descending order (using the value returned by ordering_expression).

Example

For example, the following query sorts cities in descending order by average test score, respecting the ZIP codes to which the cities belong:
SELECT MEASURES.[avg test score] ON 0, 
ORDER(homed.city.MEMBERS, MEASURES.[avg test score], DESC) ON 1 FROM patients
                            Avg Test Score
1 Pine                                75.67
2 Magnolia                            74.65
3 Cypress                             74.61
4 Centerville                         74.85
5 Cedar Falls                         74.62
6 Elm Heights                         74.36
7 Juniper                             74.52
8 Spruce                              74.14
9 Redwood                             74.16
To see a picture of the hierarchy used in this example, see the FIRSTCHILD function.
In contrast, the following example uses the BDESC keyword and disregards the hierarchy:
SELECT MEASURES.[avg test score] ON 0, 
ORDER(homed.city.MEMBERS, MEASURES.[avg test score], BDESC) ON 1 FROM patients
                            Avg Test Score
1 Pine                                75.67
2 Centerville                         74.85
3 Magnolia                            74.65
4 Cedar Falls                         74.62
5 Cypress                             74.61
6 Juniper                             74.52
7 Elm Heights                         74.36
8 Redwood                             74.16
9 Spruce                              74.14
For another example, the following query defines a string measure (as a calculated member) and then uses it with the ORDER function:
WITH MEMBER measures.stringtest AS 'IIF(MEASURES.[avg test score]<75, "low","high")' 
SELECT {MEASURES.[avg test score],MEASURES.stringtest} on 0, 
ORDER(homed.city.MEMBERS,measures.stringtest,BASC) ON 1 FROM patients
                      Avg Test Score           stringtest
1 Pine                          75.67                 high
2 Cedar Falls                   74.62                  low
3 Centerville                   74.85                  low
4 Cypress                       74.61                  low
5 Elm Heights                   74.36                  low
6 Juniper                       74.52                  low
7 Magnolia                      74.65                  low
8 Redwood                       74.16                  low
9 Spruce                        74.14                  low

See Also

Previous section   Next section