1

Here is the original question: There is a table stockprices, which contains information about trades of one company's stock. It has two columns: timestamp and price. They represent the time when a trade happened and the price of the sale. The natural order of records in the table is random and is not sorted by timestamp.

You add another column (let's call it delta) where you intend to store the difference between the current transaction price and the price of the previous transaction time-wise.

Write a single SQL statement, which will calculate the price difference and fill the column delta with it in all rows.

I write the sql as below to run on mysql, but seems current is not supported on mysql, and i can't move on, the following is the one i wrote till now to calculate the difference between the current transaction price and the price of the previous transaction time-wise:

 SELECT
   [current].timestamp,
   [current].price,
   ISNULL([next].price, 0) - [current].price
   FROM
   stockprice       AS [current]
LEFT JOIN
   stockprice       AS [next]
      ON [next].timestamp = (SELECT MIN(timestamp) FROM stockprice WHERE timestamp > [current].timestamp)
J. Steen
  • 15,470
  • 15
  • 56
  • 63
user1056648
  • 193
  • 1
  • 1
  • 7

1 Answers1

2

You're using square brackets as identifier delimiters. This syntax works only in Microsoft SQL Server and Sybase. You should use syntax that works in MySQL, which is back-quotes by default and double-quotes if you enable ANSI_QUOTES mode.

See my answer to Do different databases use different name quote?

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks, so current will be support in mysql if i change to use back-quotes? – user1056648 Nov 20 '11 at 20:51
  • Yes, MySQL supports the `AS` syntax to define table aliases so you can do a self-join like in your example. – Bill Karwin Nov 20 '11 at 20:53
  • Bill, i just met another problem, i should use `current`.price - COALESCE(`previous`.price, 0) insted of IFNULL('next'.price, 0) - 'current'.price – user1056648 Nov 20 '11 at 21:19
  • And i got Unknown column 'previous.price' in 'field list' since the first row's previous is nothing, do you have a idea to solve this? – user1056648 Nov 20 '11 at 21:20
  • There is no `previous` alias in your question. I can't help troubleshoot a query without seeing it. Please edit your question with the new query you're trying to debug. – Bill Karwin Nov 20 '11 at 23:30