window functions not available mysql 8-CodePudding

mysql> SELECT VERSION()
    -> ;
 ----------- 
| VERSION() |
 ----------- 
| 8.0.27    |
 ----------- 
1 row in set (0.02 sec)

mysql> SELECT row_number() over w as 'row_number', prod_id FROM db.table WINDOW w;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WINDOW w' at line 1

Why doesn't this work and how can I get this query to work?

CodePudding user response：

I wasn't defining the window.. once I add the AS clause this works:

 SELECT row_number() over w as 'row_number',
   prod_id
 FROM db.table
 WINDOW w AS (order by prod_id);

CodePudding user response：

You are trying to use named window function.

Named window function are basically used when there are many window function being used and you are doing same partition by or order by in every over clause of window. So named window function gives you relaxation or advantage to write partition by /order by once and have that named window in you over clause.

You probably need to implement below query for your case.

SELECT row_number() over w as 'row_number', prod_id FROM db.table WINDOW w as (order by prod_id);