DeepSee MDX Reference
ORDER
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
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:
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