Is there any syntax error because, as per my understanding, the query is perfect

Question

Please let me know if there is any syntax error because, as per my understanding, the query is perfect:

mysql> SELECT Cust_Id,Plan_Id FROM(SELECT Cust_Id,Plan_Id,ROW_NUMBER() OVER (PARTITION BY Cust_Id ORDER BY cnt DESC) AS RN FROM(SELECT Cust_Id,Plan_Id,COUNT(1) as cnt FROM customer GROUP BY (Cust_Id,Plan_Id))) WHERE RN =1;
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 '(PARTITION BY Cust_Id ORDER BY cnt DESC) AS RN FROM(SELECT Cust_Id,Plan_Id,COUNT'
at line 1

Clearly, you need to have a discussion with your DBMS (MySQL), because it disagrees with you about the perfection of your SQL. On the whole, I suspect the DBMS is correct and your claims of perfection are exaggerated. Which version of MySQL are you using? — Jonathan Leffler, Apr 23 '16 at 05:02
This is Microsoft SQL SERVER syntax not mysql. Mysql doesn't have the row_number() over () construct — Abderraouf El Gasser, Apr 23 '16 at 05:04
Apparently your understanding is incorrect. You should work on that, because understanding is important most of the time. — Ken White, Apr 23 '16 at 05:08
Can you explain what are you trying to achieve and what result do you expect with some sample data and the structure of the tables involved in your query. — Abderraouf El Gasser, Apr 23 '16 at 05:10

score 1 · Answer 1 · answered Apr 23 '16 at 05:18

MYSQL does not support ROW_NUMBER().

You have to rewrite your query as below

SELECT Cust_Id,Plan_Id 
FROM (
       SELECT Cust_Id,Plan_Id,
              @row_num := IF(@prev_value=T.Cust_Id,@row_num+1,1) AS RN,
              @prev_value := T.Cust_Id
       FROM (
           SELECT Cust_Id,Plan_Id,COUNT(1) as cnt 
           FROM customer 
           GROUP BY (Cust_Id,Plan_Id)
            ) T,
           (SELECT @row_num := 1) x,
           (SELECT @prev_value := 0) y
      )S WHERE RN =1;

Is there any syntax error because, as per my understanding, the query is perfect

1 Answers1