In one of my previous posts, I discussed on how useful GROUPING function is while writing a ROLLUP/CUBE query. One more way GROUPING function can help you is – in ordering the results returned by ROLLUP/CUBE queries.
The reason I’m writing this post is, sometime back I seen somebody writing a weird ORDER BY statement to get the desired ordering after writing a query using a ROLLUP operator. First he didn’t use the GROUPING function in SELECT statement and 2nd his ORDER BY was something like :
ORDER BY CASE ColumnName |
Just to get sub total returned by ROLLUP at the bottom of result set. Well of course if he had the knowledge about GROUPING then he wouldn’t have written such CASE statement in ORDER BY.
As you may know that GROUPING function returns 1 when the row is added by either the CUBE or ROLLUP operator, and 0 when the row is not the result of CUBE or ROLLUP. So you can easily use this property of the GROUPING for ordering the result set.
Lets have a look at the following example, 1st create some sample data.
-- create sample table Sales CREATE TABLE Sales -- Load sample data INSERT INTO Sales SELECT -- Test sample data SELECT Id, FName, Zone, Sale |
The sample data :
ID | FName | Zone | Sale |
1 | Mangal | East | 20 |
2 | Mangal | East | 150 |
3 | Mangal | West | 50 |
4 | Ram | East | 45 |
5 | Ram | NULL | 80 |
6 | Ram | NULL | 40 |
7 | Sachin | West | 50 |
8 | Sachin | West | 40 |
And here is the expected output :
FName | Zone | Total |
Mangal | East | 170 |
Mangal | West | 50 |
Mangal | All Zone | 220 |
Ram | East | 45 |
Ram | Unknown | 120 |
Ram | All Zone | 165 |
Sachin | West | 90 |
Sachin | All Zone | 90 |
All Names | All Zone | 475 |
As you can see in the expected output, all the FNames are ordered in ascending order and their total SUM is at the bottom, same for the Zone column. For ordering the result in that way just use the GROUPING(column_name) in ORDER BY just before the column_name. See the following query, esp the ORDER BY clause:
SELECT CASE GROUPING(fname) |
Simple, isn’t it? Now you don’t need to write a CASE statement in ORDER BY, just use the GROUPING function. If you will be doing the ORDERING in application layer, then you will need to get in the GROUPING(fname) and GROUPING(zone) column in the SELECT list as well.
Mangal