- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- What is the use of WITH ROLLUP modifier in MySQL?
- Implement MySQL ORDER BY without using ASC or DESC?
- Resolve Syntax error near “ORDER BY order DESC” in MySQL?
- While using the ROLLUP modifier, is it possible to use a MySQL ORDER BY clause to sort the result?
- How to sort varchar numeric columns by DESC or ASC in MySQL?
- MySQL “order by” inside of “group by”? Is it possible?
- What would be the effect on the output of MySQL LAST_INSERT_ID() function in the case on multiple-row insert?
- Can we use IFNULL along with MySQL ORDER BY?
- MySQL IF/WHEN/ELSE/OR with ORDER BY FIELD
- Why can't we use column name “desc” in MySQL?
- Can we use “rank” as column name with MySQL8?
- MySQL ORDER BY with CASE WHEN
- What would be the difference between default output format when running MySQL in batch mode or interactively?
- Order dates in MySQL with the format “01 August 2019”?
- Displaying only a list of records in ASC order with MySQL