SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to sort specific column under GROUPING SETS?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dchencm
Starting Member

6 Posts

Posted - 08/21/2014 :  13:07:48  Show Profile  Reply with Quote
If I remember correctly, in SQL Server, it is not recommended to use "ORDER BY" when there's already a GROUP BY GROUPING SETS.

I have two columns: [UPC#] & [Description] both are varchar.

My grouping sets is like this:

GROUP BY 
GROUPING SETS 
(
    ([UPC],[Description])
    ,()     
)


I don't have 'ORDER BY' but it's automatically sorting the Description column.

If I added a 3rd column, sum(Qty), then it doesn't sort by Description anymore. But if I added


ORDER BY [Description]


then the grand total of sum(Qty) will be at the first row instead of the last.

Is there a way to sort the Description column and still let the grand total of sum(Qty) be at the last row instead?

Thanks.

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 08/21/2014 :  16:22:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
GROUP BY does not sort data. Sets have no order.
Your data will be outputed in the order the CPU processes the groups, which can be different between executions.
Most common when statistics change.

Use ORDER BY GROUPING(...)



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000