Your Location is: Home > Mysql

Sort on a WITH ROLLUP

From: Norway View: 1323 David542 

Question

I can do the following fine:

SELECT provider_id, count(*) cnt 
FROM title 
GROUP BY provider_id WITH ROLLUP

However, it doesn't seem to support ordering after a rollup:

SELECT provider_id, count(*) cnt 
FROM title 
GROUP BY provider_id WITH ROLLUP 
ORDER BY count(*) DESC

Incorrect usage of CUBE/ROLLUP and ORDER BY

Two questions related to this:

  1. Are you not allowed to use an order by in the same select statement as a with rollup? Or, is this something specific to mysql5.7 -- in that it doesn't support this feature -- but other DBs do dupport this?

  2. Given this constraint, is the only way to do the sort by using a subselect?

    SELECT * FROM  (<rollup query>) _ ORDER BY cnt DESC
    

Related, but doesn't really answer the above question (as I already have the query above): https://stackoverflow.com/a/1768565/651174.

Best answer