MDX TOP 1 - Return TOP 1 from a CUBE

Question

Is there a way I limit the values returned by an MDX query in a way similar to a SQL TOP 1 query.

SELECT TOP 1 Names From tbl_Names Order by names DESC

e.g. is the a MDX statement that could do the same?

Answer 1

You can use the TOPCOUNT and BOTTOMCOUNT functions. Basically you specify which dimension or set you want the TOP or BOTTOM n from and then specify some measure or numeric expression with which to order the resultset.

Answer 2

Try something like:

From the web..

WITH
SET [TCat] AS 
    TopCount([Product].[Subcategory].[Subcategory],10,[Measures].[Sales Amount]) 
MEMBER [Product].[Subcategory].[Other] AS
    Aggregate([Product].[Subcategory].[Subcategory] - TCat)
SELECT { 
        [Measures].[Sales Amount] 
    } ON COLUMNS, 
    TCat + [Other] ON ROWS 
FROM [Adventure Works]