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?
MySQL and window functions
MySQLwindow functions
Related Question
- MySQL Full Text Search – Execute Code Only if Full Text Search is Available
- SQL Server – Date Range Rolling Sum Using Window Functions
- MySQL – How to Check if JSON Feature Exists and/or Version
- MySQL InnoDB – AUTO_INCREMENT Columns in Multiple-Column Index Support Status
- MySQL select * from table without conditions does not return rows right away
- MySql doesn’t use extra CPU
Best Answer
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 onGROUP_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()
:DISTINCT
ORDER BY ... ASC/DESC
SEPARATOR
(*) Support for Window Functions has been added in MySQL 8