While using the ROLLUP modifier, is it possible to use a MySQL ORDER BY clause to sort the result?

Actually ROLLUP and ORDER BY are mutually exclusive in MySQL hence it is not a good practice to use both of them in a query. But still, if we use ROLLUP in ORDER BY then the main disadvantage is that the summary rows would get sorted along with the rows they are calculated. It is also significant to notice that the sort order will decide the position of summary rows.

The summary rows would be at the beginning of ascending order and at the end of descending order. Consider the following example to understand it more clearly −

mysql> Select * from (Select sr, SUM(PRICE) AS PRICE from ratelist GROUP BY sr WITH ROLLUP) AS Price ORDER BY Sr DESC;
+------+-------+
| sr   | PRICE |
+------+-------+
|    5 |   250 |
|    4 |   850 |
|    3 |  1005 |
|    2 |   630 |
|    1 |   502 |
| NULL |  3237 |
+------+-------+
6 rows in set (0.00 sec)

mysql> Select * from (Select sr, SUM(PRICE) AS PRICE from ratelist GROUP BY sr WITH ROLLUP) AS Price ORDER BY Sr ASC;
+------+-------+
| sr   | PRICE |
+------+-------+
| NULL |  3237 |
|    1 |   502 |
|    2 |   630 |
|    3 |  1005 |
|    4 |   850 |
|    5 |   250 |
+------+-------+
6 rows in set (0.01 sec)

usharani

Updated on: 22-Jun-2020

241 Views

Kickstart Your Career

Get certified by completing the course

Get Started