StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUnknown column error when trying to pair current row with next row in time-ordered table in SQL
text
Body
copied!<p>There is a table stockprice, 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.</p> <p>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.</p> <p>Write a single SQL statement, which will calculate the price difference and fill the column delta with it in all rows.</p> <p>I write the sql as below to run 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, mysql give me the "Unknown column 'previous.price' in 'field list'" error since the first row's previous is nothing, can some expert have a idea to solve this?:</p> <pre><code> SELECT `current`.timestamp, `current`.price, `current`.price - IFNULL(`previous`.price, 0) AS delta FROM stockprice AS `current` LEFT JOIN stockprice AS `next` ON `next`.timestamp = (SELECT MIN(timestamp) FROM stockprice WHERE timestamp > `current`.timestamp) My database like: timestamp price 2011-10-27 00:00:00 12424 2011-10-24 00:00:00 15464 2011-10-25 00:00:00 543464 2011-10-23 00:00:00 58791 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload