Finding the difference between two values in the same column in MySQL

mysql

Solution

Ok, got home, and was able to figure this out.

SELECT stock_id, t1.price AS `then`, t2.price AS `now`, ROUND(t2.price - t1.price, 2) AS `difference`
FROM (
        SELECT stock_id, price, date FROM share_prices sp
        WHERE  date = (SELECT MIN(date) FROM share_prices sp2
                       WHERE date BETWEEN '2010/02/23 10:00:00'
                       AND '2010/02/24 10:00:00'
                       AND sp2.stock_id = sp.stock_id)
    ) t1
    JOIN
    (
        SELECT stock_id, price, date FROM share_prices sp
        WHERE  date = (SELECT MAX(date) FROM share_prices sp2
                       WHERE date BETWEEN '2010/02/23 10:00:00'
                       AND '2010/02/24 10:00:00'
                       AND sp2.stock_id = sp.stock_id)
    ) t2 USING(stock_id)
ORDER BY `difference` DESC

Uses the results from 2 subqueries, each with their own subquery to the first and last, respectively, record for that range.

I was using `integer` for `stock_id`, `float` for `price` and `timestamp` for date, since there may be issues (notably with the MIN and MAX) with other data types.

Problem

I have a simple table called that contains share prices in MySQL: ``` Table `share_prices` +----------+-------+---------------------+ | stock_id | price | date | +----------+-------+---------------------+ | 1 | 0.05 | 2010-02-24 01:00:00 | | 2 | 3.25 | 2010-02-24 01:00:00 | | 3 | 3.30 | 2010-02-24 01:00:00 | | 1 | 0.50 | 2010-02-23 23:00:00 | | 2 | 1.90 | 2010-02-23 23:00:00 | | 3 | 2.10 | 2010-02-23 23:00:00 | | 1 | 1.00 | 2010-02-23 19:00:00 | | 2 | 1.00 | 2010-02-23 19:00:00 | | 3 | 1.00 | 2010-02-23 19:00:00 | +----------+-------+---------------------+ ``` Every time a share price is updated, a new row is inserted into the table. With this structure, how can I return a query that shows the price change in the last 24 hours? The desired result would be: ``` +----------+------+------+------------+ | stock_id | then | now | difference | +----------+------+------+------------+ | 3 | 1.00 | 3.30 | 2.30 | | 2 | 1.00 | 3.25 | 2.25 | | 1 | 1.00 | 0.05 | -0.95 | +----------+------+------+------------+ ``` What's the best way to go about this? Some kind of join? A sub-query? What I think I'm aiming for is to essentially query once to get `then`, query again to get `now` and then somehow glue it all together at the end. Edit: I need to account for negative changes too.

Original source

Related problems