InterSystems MDX Reference
BOTTOMCOUNT


Sorts a set and returns a subset from its lowervalued end, given a desired element count.
BOTTOMCOUNT(set_expression, element_count, optional_ordering_expression)


The function uses this argument to determine the number of elements to return in the subset. If this argument is greater than the number of elements, all elements are returned.

The function evaluates this expression for each element of the set and sorts the elements of the set in ascending order according to this value. Any hierarchies are ignored.
If this argument is omitted, the function uses the current order of the set elements (and this function behaves like the
TAIL function).
First consider the following query and the results it returns:
SELECT MEASURES.[%COUNT] ON 0,
BOTTOMCOUNT(birthd.decade.MEMBERS, 100, MEASURES.[%COUNT]) ON 1
FROM patients
Patient Count
1 1910s 71
2 2010s 155
3 1920s 223
4 1930s 572
5 1940s 683
6 1950s 1,030
7 1980s 1,400
8 1990s 1,413
9 2000s 1,433
10 1960s 1,500
11 1970s 1,520
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.
SELECT MEASURES.[%COUNT] ON 0,
BOTTOMCOUNT(birthd.decade.MEMBERS, 3, MEASURES.[%COUNT]) ON 1
FROM patients
Patient Count
1 1910s 71
2 2010s 155
3 1920s 223
This query selects three members from the lowervalued end of the set.