order by not working with grouping

Question

User1226526163 posted

Hi,

I have a dataset where ido an order by col X. In the report i group the result by col Y but then it doesnt order by col x its orderering by col Y. Is there a way to get this thing working?

Thanks

Answer 1

User-843484705 posted

I have an option or solution for you

you can use like

select a,b,c, sum from (SELECT a, b, c, SUM ( salary) sal FROM T GROUP BY ROLLUP (a,b,c)) as NewTab order by a

or check here http://msdn.microsoft.com/en-us/library/ms177673.aspx

Answer 2

User1471008070 posted

Hi,

As you add a group ColY, so it doesn't sort the data based on the colx, this is expected, if you want to sort the data by colx inside the group, please follow this:

1. If you are using Matrix, please add a sort to matrix's group, steps are: right-click the ColY in the row group pane, and then select Group Properties, move to sorting tab, click Add button to add a sort, in the drop-down list of Sort by, please select colx datafiled.

2. If you are using Tablle, please add a sort to table's detail row, steps are: right-click the (Details) in the row group pane, and then select Group Properties, move to sorting tab, click Add button to add a sort, in the drop-down list of Sort by, please select colx datafiled.

If you have any question about the steps, please feel free to ask.

Thanks,
Challen Fu