BOTTOMCOUNT - InterSystems MDX Reference - InterSystems IRIS Data Platform 2018.1

docs.intersystems.com

Home / InterSystems MDX Reference / MDX Functions / BOTTOMCOUNT

InterSystems MDX Reference

BOTTOMCOUNT

InterSystems: The power behind what matters

Sorts a set and returns a subset from its lower-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

BOTTOMCOUNT(set_expression, element_count, optional_ordering_expression)

Where:

set_expression is an expression that evaluates to a set of members or tuples.
element_count is an integer literal.

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.
optional_ordering_expression is a numeric-valued expression that determines the order of the set elements.

Typically, this expression has the form [MEASURES].[measure_name ]

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).

Example

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.

Next, consider a similar query, using count_expression equal to 3:

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 lower-valued end of the set.