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

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]

Answer 2

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.