ORDER - DeepSee MDX Reference - Caché & Ensemble 2018.1

docs.intersystems.com

DeepSee MDX Reference

ORDER

[Home] [Back] [Next]

InterSystems: The power behind what matters

InterSystems DeepSee > DeepSee MDX Reference > MDX Functions > ORDER

Search:

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