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

  • 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.

protected by Community Oct 8 '17 at 11:10

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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