DeepSee MDX Reference
TOPCOUNT
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Sorts a set and returns a subset from its higher-valued end, given a desired element count.
Returned Type
This function returns a set of members or tuples, depending on the set used.
Syntax and Details
TOPCOUNT(set_expression, element_count, optional_ordering_expression)
Where:
Example
First consider the following query and the results it returns:
SELECT MEASURES.[%COUNT] ON 0, 
TOPCOUNT(birthd.decade.MEMBERS, 100, MEASURES.[%COUNT]) ON 1 FROM patients
                             Patient Count
 1 1970s                              1,520
 2 1960s                              1,500
 3 2000s                              1,433
 4 1990s                              1,413
 5 1980s                              1,400
 6 1950s                              1,030
 7 1940s                                683
 8 1930s                                572
 9 1920s                                223
10 2010s                                155
11 1910s                                 71
Because count_expression is greater than the number of members, all members are returned. The members are sorted in ascending order according to the value of the %COUNT measure.
Next, consider a similar query, using count_expression equal to 3:
SELECT MEASURES.[%COUNT] ON 0, 
TOPCOUNT(birthd.decade.MEMBERS, 3, MEASURES.[%COUNT]) ON 1 FROM patients
                             Patient Count
1 1970s                               1,520
2 1960s                               1,500
3 2000s                               1,433
This query selects three members from the higher-valued end of the set.
See Also