Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
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 at 5:57
add comment (requires an account with 50 reputation)

2 Answers

up vote 2 down vote

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.
share|improve this answer
add comment (requires an account with 50 reputation)
up vote -1 down vote

No there is no analytic functions in MySQL. But you can override this feature. See below: analytic function

share|improve this answer
In your link the OVER() is used. But it does not seem to work when I tried it – Cratylus Apr 16 at 16:03
Emulating Analytic Functions in MySQL refer this one maybe it will be helpfull – Shahriyar Rzayev Apr 16 at 16:10
Just for what it's worth, your links are right outdated, you should really reference that those are for older versions. – jcolebrand♦ Apr 17 at 21:01
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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