CODEWITHSUNDEEP
mysql
secret
secret

Reputation: 786

Syntax error near (

I am trying to query something and I am not so good with Mysql, so I was wondering if someone can tell me what is the issue here. Here is my table:

 create table #transfers (
     sender varchar not null,
     recipient varchar not null,
          date date not null,
          amount integer not null
      );


INSERT INTO #transfers(sender,recipient,date,amount) VALUES ('Smith','Williams','2000-01-01',200);
INSERT INTO #transfers(sender,recipient,date,amount) VALUES ('Smith','Taylor','2002-09-27',1024);
INSERT INTO #transfers(sender,recipient,date,amount) VALUES ('Smith','Johnson','2005-06-26',512);
INSERT INTO #transfers(sender,recipient,date,amount) VALUES ('Williams','Johnson','2010-12-17',100);
INSERT INTO #transfers(sender,recipient,date,amount) VALUES ('Williams','Johnson','2004-03-22',10);
INSERT INTO #transfers(sender,recipient,date,amount) VALUES ('Brown','Johnson','2013-03-20',500);
INSERT INTO #transfers(sender,recipient,date,amount) VALUES ('Johnson','Williams','2007-06-02',400);
INSERT INTO #transfers(sender,recipient,date,amount) VALUES ('Johnson','Williams','2005-06-26',400);
INSERT INTO #transfers(sender,recipient,date,amount) VALUES ('Johnson','Williams','2005-06-26',200);

This is the query:

WITH cte AS
(
  SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY recipient ORDER BY amount DESC)
  FROM #transfers
)
SELECT recipient
FROM cte
WHERE rn <= 3
GROUP BY recipient
HAVING SUM(amount) >= 1024
ORDER BY recipient

However I get this error:

near "(": syntax error

Upvotes: 1

Views: 563

Answers (2)

Maulik
Maulik

Reputation: 907

use this create table query

create table transfers (
id INT NOT NULL AUTO_INCREMENT,
     sender VARCHAR(100) NOT NULL,
     recipient VARCHAR(100) NOT NULL,
          date DATE, 
          amount VARCHAR(100) NOT NULL,
          PRIMARY KEY ( id )
      );

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

CTEs and ROW_NUMBER are not available in MySQL.

Try with Subquery:

SELECT recipient
FROM 
(
     SELECT t.*, @rownum := @rownum + 1 AS rank
     FROM #transfers t, (SELECT @rownum := 0) r
)X
WHERE rank <= 3
GROUP BY recipient
HAVING SUM(amount) >= 1024
ORDER BY recipient

Fiddle Demo

Upvotes: 1

Related Questions