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

MySQLMySQLi Database

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)
raja
Published on 22-Feb-2018 06:05:54
Advertisements