WHERE TOP or BOTTOM(n) IN

WHERE TOP|BOTTOM IN is a ranking filter. It limits the inputs into a metric’s computations to those values that are the top or bottom n rankings according to the ranking criteria that you specify. This filter supports percent parameters: n%.

Syntax

SELECT … WHERE TOP(…) IN … WITHIN …
SELECT metric1 WHERE TOP(n) IN (SELECT metric2 BY attribute1, attribute2...) WITHIN (...)

Here, metric1 represents the metric whose value is actually returned. The value returned depends on which values are included in its computations. These inputs are determined by the filtering criteria statement that follows the IN keyword in parentheses.

The attributes following BY are those that will be ranked according to their metric2 values. Whichever attribute values are associated with metric2 values that fall in the TOP(n) or BOTTOM(n) are then included in metric1 computations – determining the final output.

Examples

SELECT #tickets WHERE TOP(5) IN (SELECT #tickets 2 BY assignee) WITHIN (ticket group, all other)

This example returns  TOP(#) assignees by EACH group. 

SELECT Amount WHERE TOP(5) IN (SELECT Avg. Won BY Product)

MAQL also supports using an OF keyword following the TOP|BOTTOM ranking filter. This changes the way the metric is interpreted by the query engine. With OF you can carry out rankings by more than one metric. Likewise, the ranking metric is interpreted as a submetric, rather than a subreport (as is the case for TOP|BOTTOM in conjunction with the IN keyword).

This may also help: