# In an MDX query how can I get the top 3 sales years based on the order quantity?

Q: In an MDX query how can I get the top 3 sales years based on the order quantity?

A: By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are "OrderBy" and "OrderByAttribute". Lets say we want to see order counts for each year. In Adventure Works MDX query would be:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1

And the result for this query is:

 Reseller Order Quantity CY 2001 10,835 CY 2002 58,241 CY 2003 100,172 CY 2004 45,130

In the results above years are ordered in ascending order 2001, 2002, 2003 and 2004. If we want to get results ordered based on [Reseller Order Quantity] measure, we can use MDX function ORDER. This function has following parameters (copied from BOL):

Numeric expression syntax
Order(Set_Expression, Numeric_Expression [ , { ASC | DESC | BASC | BDESC } ] )

String expression syntax
Order(Set_Expression, String_Expression [ , { ASC | DESC | BASC | BDESC } ] )

As we want to order dimension members based on measure, we will use [Measures].[Reseller Order Quantity] for Numeric_Expression and we will use DESC flag to order in descending order. So new MDX query is:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, ORDER([Date].[Calendar].[Calendar Year].Members, [Measures].[Reseller Order Quantity], DESC) ON 1

And new result:

 Reseller Order Quantity CY 2003 100,172 CY 2002 58,241 CY 2004 45,130 CY 2001 10,835

To get top 3 members, we will use function Head(<Set>,<member count>) that will return first specified number of members from the set. Updated MDX query:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, Head(ORDER([Date].[Calendar].[Calendar Year].Members, [Measures].[Reseller Order Quantity], DESC),3) ON 1

And new updated result:

 Reseller Order Quantity CY 2003 100,172 CY 2002 58,241 CY 2004 45,130

And Romuald Coutaud suggested even shorter query to do the same thing. Instead of Head + Order we can use TopCount function. New query is:

SELECT
{[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1