30

It seems that MySQL does not support window functions.
E.g. the simple: COUNT(*) OVER() AS cnt does not work.
What I am not sure is if this applies to commercial version as well (I assume the community version is limited subset).
If not, how does one work around this missing feature?

  • 3
    The community edition of MySQL Server is not a limited subset in any meaningful sense. The differences are in add-ons and plugins that do not affect core functionality. – Michael - sqlbot Apr 17 '13 at 5:57
  • Have a look at this workaround here and here – StuartLC Jan 26 '14 at 19:45
  • Actually, MySQL9 supports it. I have been using pandas instead which kind of sucks. – Andrew Scott Evans Sep 18 '17 at 18:56
  • 1
    MySQL 8 supports window function. For reference: dev.mysql.com/doc/refman/8.0/en/window-functions.html – gvgvgvijayan May 8 '18 at 5:51
  • MySQL supports window functions since version 8.0. This link might help. – Hamid Mohayeji Nov 23 '18 at 22:02
36

MySQL does not support Window Functions(*). There is what we call "a poor man's window function" in the form of GROUP_CONCAT().

There are plenty of tricks using GROUP_CONCAT to emulate window functions. They are not as pretty (syntactically) and are sometimes too limited. I've written a few. See my blog post complaining about the missing window functions, and linking to various solutions based on GROUP_CONCAT.

In particular, Selecting a specific non aggregated column data in GROUP BY and SQL: selecting top N records per group, another solution might be of interest to you and could give you a kick start.

Things you should note about GROUP_CONCAT():

  • Can use DISTINCT
  • Can use ORDER BY ... ASC/DESC
  • Can set SEPARATOR
  • As any aggregation function - it discards NULL values; plenty tricks on that.

(*) Support for Window Functions has been added in MySQL 8

| improve this answer | |
  • MySQL supports window functions as of Version 8 – Max Aug 23 '18 at 16:12
  • GROUP_CONCAT is not a window function, it is an ordered set function. – SQLRaptor Sep 3 '18 at 17:12
  • 1
    @SQLRaptor no one claimed it is. – ypercubeᵀᴹ Sep 3 '18 at 20:41
11

It really should be noted that MariaDB 10.2 (released in 2017 May) has window functions. That's certainly one avenue to pursue if you need MySQL and window functions.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.