Query working in SQL Server but not working in mySQL

Question

A company has a record of it's customers and their orders. I am writing a query to get the names of those customer(s) and order(s), who have the maximum price order within the first ten years of the first order. This select query is working in SQL Server but I am getting an error in MySQL

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 '1 C.NAME, O.PRICE, O.ORDER_DATE FROM ORDERS O, CUSTOMERS C WHERE O.PRICE = (SEL' at line 1

CREATE TABLE CUSTOMERS(ID varchar(10), NAME varchar(50), ORDER_ID varchar(20));
  INSERT INTO CUSTOMERS VALUES('1','Tom','abcd');
  INSERT INTO CUSTOMERS VALUES('52','Lucy','fghi');
  INSERT INTO CUSTOMERS VALUES('43','Frank','klmn');
  INSERT INTO CUSTOMERS VALUES('64','Jane','opqr');
  INSERT INTO CUSTOMERS VALUES('565','Robert','xyz');

CREATE TABLE ORDERS(ID varchar(20), PRICE int, ORDER_DATE date);
  INSERT INTO ORDERS VALUES('abcd', 100, '1987-10-02');
  INSERT INTO ORDERS VALUES('fghi', 200, '1987-10-06');
  INSERT INTO ORDERS VALUES('klmn', 20, '1998-10-22');
  INSERT INTO ORDERS VALUES('opqr', 50, '1988-10-14');
  INSERT INTO ORDERS VALUES('xyz', 300, '1999-10-02');
  INSERT INTO ORDERS VALUES('fghi', 250, '1995-10-06');
  INSERT INTO ORDERS VALUES('klmn', 40, '1993-11-22');
  INSERT INTO ORDERS VALUES('abcd', 70, '1989-12-14');
  INSERT INTO ORDERS VALUES('klmn', 590, '1999-01-22');
  INSERT INTO ORDERS VALUES('opqr', 540, '1988-10-14');
  INSERT INTO ORDERS VALUES('fghi', 540, '1986-10-14');
  INSERT INTO ORDERS VALUES('abcd', 470, '1979-02-14');

SELECT C.NAME, O.PRICE
FROM ORDERS O, CUSTOMERS C 
WHERE O.PRICE = 
(
    SELECT MAX(PRICE) FROM ORDERS 
    WHERE ORDER_DATE BETWEEN 
    (SELECT TOP 1 ORDER_DATE FROM ORDERS ORDER BY ORDER_DATE) 
    AND 
    (SELECT TOP 1 DATEADD(year, 10, ORDER_DATE) FROM ORDERS ORDER BY ORDER_DATE)
) 
AND C.ORDER_ID = O.ID ORDER BY O.ORDER_DATE;

MySQL doesn't support TOP

Nick
– Nick

2019-08-28 05:11:20 +00:00
Commented Aug 28, 2019 at 5:11 — Nick
– Nick, Commented Aug 28, 2019 at 5:11
Use LIMIT instead of TOP

Shivani
– Shivani

2019-08-28 05:11:55 +00:00
Commented Aug 28, 2019 at 5:11 — Shivani
– Shivani, Commented Aug 28, 2019 at 5:11
(SELECT ORDER_DATE FROM ORDERS ORDER BY ORDER_DATE LIMIT 1)

Ed Bangga
– Ed Bangga

2019-08-28 05:13:41 +00:00
Commented Aug 28, 2019 at 5:13 — Ed Bangga
– Ed Bangga, Commented Aug 28, 2019 at 5:13

mkRabbani · Accepted Answer · 2019-08-28 05:43:42Z

1

You can try this below option-

SELECT C.NAME, O.PRICE
FROM ORDERS O
INNER JOIN CUSTOMERS C ON C.ORDER_ID = O.ID
/*Comma separated joins are old fashion now.
You should use Standard joining as shown*/
WHERE O.PRICE = 
(
    SELECT MAX(PRICE) 
    FROM ORDERS 
    WHERE ORDER_DATE BETWEEN 
    (SELECT MIN(ORDER_DATE) FROM ORDERS) 
    AND 
    (SELECT DATE_ADD(MIN(ORDER_DATE), INTERVAL 10 year) FROM ORDERS)
    /*In selecting date range, you will gain some performance here
    As this will select only the minimum date where as you are 
    selecting all dates first and keeping one row from them

    Also you are applying DATE_ADD on all rows of your table
    to generate the Upper date range which is also performance issue*/
) 
ORDER BY O.ORDER_DATE;

edited Aug 28, 2019 at 5:43

answered Aug 28, 2019 at 5:37

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sujay Over a year ago

Thanks @mkRabbani, I accepted your answer as it has taken 2ms whereas my answer is taking 11ms so definitely your solution is better than me.

mkRabbani Over a year ago

You are most welcome. Nice to hear that it helped :)

Sujay · Accepted Answer · 2019-08-28 05:18:03Z

0

Thanks @Nick, @Shivani for the quick support. corrected my query and it is working.

SELECT C.NAME, O.PRICE, O.ORDER_DATE
FROM ORDERS O, CUSTOMERS C 
WHERE O.PRICE = (
    SELECT MAX(PRICE) 
    FROM ORDERS 
    WHERE ORDER_DATE BETWEEN 
        (SELECT ORDER_DATE FROM ORDERS ORDER BY ORDER_DATE LIMIT 1) 
    AND 
        (SELECT DATE_ADD(ORDER_DATE, INTERVAL 10 YEAR) FROM ORDERS ORDER BY ORDER_DATE LIMIT 1)
) 
AND C.ORDER_ID = O.ID order by O.ORDER_DATE;

answered Aug 28, 2019 at 5:18

Sujay

2,5103 gold badges29 silver badges47 bronze badges

Comments

Primit · Accepted Answer · 2019-08-28 06:06:08Z

0

mysql doesn't support Top. use LIMIT like this

 SELECT C.NAME, O.PRICE
FROM ORDERS O, CUSTOMERS C 
WHERE O.PRICE = 
(
    SELECT MAX(PRICE) FROM ORDERS 
    WHERE ORDER_DATE BETWEEN 
    (SELECT ORDER_DATE FROM ORDERS ORDER BY ORDER_DATE LIMIT 1) 
    AND 
    (SELECT DATEADD(year, 10, ORDER_DATE) FROM ORDERS ORDER BY ORDER_DATE LIMIT 1)
) 
AND C.ORDER_ID = O.ID ORDER BY O.ORDER_DATE;

answered Aug 28, 2019 at 6:06

Primit

8657 silver badges13 bronze badges

Comments

Primit · Accepted Answer · 2019-08-28 10:45:22Z

0

SELECT C.NAME, O.PRICE
FROM ORDERS O, CUSTOMERS C 
WHERE O.PRICE = 
(   SELECT MAX(PRICE) FROM ORDERS 
    WHERE ORDER_DATE BETWEEN 
    (SELECT  ORDER_DATE FROM ORDERS ORDER BY ORDER_DATE LIMIT 1) 
    AND 
    (SELECT   DATEADD(year, 10, ORDER_DATE) FROM ORDERS ORDER BY ORDER_DATE LIMIT 1)
) 
AND C.ORDER_ID = O.ID 
ORDER BY O.ORDER_DATE

edited Aug 28, 2019 at 10:45

Primit

8657 silver badges13 bronze badges

answered Aug 28, 2019 at 5:30

mithu

14 bronze badges

1 Comment

Sujay Over a year ago

Range is incorrect as i am filtering 10 years data WHERE ORDER_DATE BETWEEN (SELECT ORDER_DATE FROM ORDERS ORDER BY ORDER_DATE LIMIT 1) AND (SELECT ORDER_DATE FROM ORDERS ORDER BY ORDER_DATE LIMIT 1)

José Sanguino · Accepted Answer · 2023-01-11 22:47:08Z

0

In MySQL DB:

SELECT C.NAME, O.PRICE 
FROM CUSTOMERS C, ORDERS O 
WHERE C.ORDER_ID = O.ID AND
      O.ORDER_DATE <= DATE_ADD((SELECT MIN(ORDER_DATE) FROM ORDERS), interval 10 year)
ORDER BY O.PRICE DESC LIMIT 1

answered Jan 11, 2023 at 22:47

José Sanguino

11 bronze badge

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Query working in SQL Server but not working in mySQL

5 Answers 5

2 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related