I have two tables:
PRICE
ticker date close
AAPL 3/20/2019 $100
AAPL 3/21/2019 $101.5
AAPL 3/22/2019 $106.5
GOOG 3/20/2019 $100
GOOG 3/21/2019 $130
GOOG 3/22/2019 $110
MSFT 3/20/2019 $184.5
MSFT 3/21/2019 $188.5
MSFT 3/22/2019 $210
IBM 3/20/2019 $72
IBM 3/21/2019 $70
IBM 3/22/2019 $10
STOCK
ticker exchange
AAPL NASDAQ
GOOG NASDAQ
MSFT NASDAQ
IBM NYSE
and I want to return the ticker that is in 'NYSE' with the highest increase in closing price from 2019-03-20 to 2019 03-21. I have:
SELECT DISTINCT T.ticker
FROM PRICE T
WHERE T.ticker IN SELECT D1.ticker, MAX(D1.close-D2.close)
FROM PRICE D1, PRICE D2, STOCK S
WHERE S.exchange='NYSE' AND D1.date = '2019-03-21' AND D2.date = '2019-03-20' AND D1.ticker = D2.ticker GROUP BY D1.ticker)
GROUP BY T.ticker;
But it returns '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 'SELECT D1.ticker, MAX(D1.close-D2.close) FROM PRICE D1, PRICE D2, STOCK S WHERE ' at line 1'
IBM should return as output, as it is the only ticker in NYSE.
I'm completely new to SQL and any suggestions would be appreciated :)
Use a self-join:
select p1.ticket
from price p1 join
price p2
on p1.ticker = p2.ticker and
p1.date = '2019-03-20' and
p2.date = '2019-03-21'
order by p2.price - p1.price desc
limit 1;
You only need the join
to stock
if you need the exchange (which your query suggests you want, but the explanation doesn't mention):
select p1.ticket
from price p1 join
price p2
on p1.ticker = p2.ticker and
p1.date = '2019-03-20' and
p2.date = '2019-03-21' join
stock s
on p1.ticker = s.ticker
where s.exchange = 'NYSE'
order by p2.price - p1.price desc
limit 1;