0

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.

Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
David542
  • 104,438
  • 178
  • 489
  • 842
  • which mysql version are you using 8.0.22 seems to have no problem with it. – nbk Nov 21 '20 at 21:39
  • @nbk -- 5.7.... (Maybe that answers my first question -- that it's part of the sql standard but just not supported by mysql < 8) – David542 Nov 21 '20 at 21:50
  • the documentation states that 5.7.32 has it also – nbk Nov 21 '20 at 21:54
  • @nbk -- ah, ok. Actually I'm one off, sorry about that: `select version() 5.6.44-log` – David542 Nov 21 '20 at 22:00
  • @nbk could you please share the link where it says which version supports it? – David542 Nov 21 '20 at 22:00
  • you have to read the maual https://dev.mysql.com/doc/refman/5.6/en/select.html but there is also written that it is supported – nbk Nov 21 '20 at 22:07
  • but it is only supported in mysql 8 correctly sorry see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e8205e515bf29427973902a3f216457f prior versions give the same error – nbk Nov 21 '20 at 22:10
  • 1
    Versions prior to 8.0 does not support ORDER BY COUNT(*) in shown case, and subquery is the only option. Version 8+ allows such sorting. It seems that ROLLUP in those ancient version acts over final (and already sorted!) rowset (like window functions) – Akina Nov 21 '20 at 22:13
  • 1
    @Akina I see -- want to post in an answer and I can accept it? – David542 Nov 21 '20 at 22:16
  • 1
    "Previously, MySQL did not allow the use of DISTINCT or ORDER BY in a query having a WITH ROLLUP option. This restriction is lifted in MySQL 8.0.12 and later. (Bug #87450, Bug #86311, Bug #26640100, Bug #26073513)" ([source](https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html#:~:text=The%20GROUP%20BY%20clause%20permits,analysis%20with%20a%20single%20query.)) – Luuk Nov 22 '20 at 10:09

0 Answers0