What would be the effect on summary output when I use explicit sort order (ASC or DESC) with column names in the GROUP BY list along with “WITH ROLLUP” modifier?

In the case, where we use explicit sort order (ASC or DESC) with column names in the GROUP BY list along with the “WITH ROLLUP” modifier, the summary rows added by ROLLUP still appear after the rows from which they calculated regardless of the sort order.

As we know that the default sort order is ascending hence in the example below if we will not use any explicit sort order then the output would be as follows −

mysql> Select sr, SUM(Price) AS Price from ratelist Group by sr with rollup;
+-----+-------+
| sr  | Price |
+-----+-------+
|  1  |   502 |
|  2  |   630 |
|  3  |  1005 |
|  4  |   850 |
|  5  |   250 |
|NULL |  3237 |
+-----+-------+
6 rows in set (0.00 sec)

Now after defining the sort order as DESC we will get the output from same above query as follows −

mysql> Select sr, SUM(Price) AS Price from ratelist Group by sr DESC with rollup;
+-----+-------+
| sr  | Price |
+-----+-------+
|  5  |   250 |
|  4  |   850 |
|  3  |  1005 |
|  2  |   630 |
|  1  |   502 |
|NULL |  3237 |
+-----+-------+
6 rows in set (0.00 sec)

It can be observed from the output that despite the sort order, changed to descending, the summary rows appeared after the rows from which they are calculated.

Nikitha N

Updated on: 22-Jun-2020

69 Views

Kickstart Your Career

Get certified by completing the course

Get Started