Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.

For information on migrating to InterSystems IRIS, see How to Migrate to InterSystems IRIS, available on the WRC Distributions page (login required).

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

Feedback