SQL for price difference calculation

Question

I've got two tables that I'm trying to grab data from. The first is a 'titles' table, which represents product information (name, unique ID, etc). The second is a 'prices' table which collects price information for various currencies (each product can have multiple historic entries in the prices table).

I've written a fairly long-winded SQL statement to grab the latest price changes across products, but there are some issues that hopefully more experienced users will be able to help me out with:

                SELECT 
                    t.id, 
                    t.name, 
                    t.type, 
                    p.value,
                    (SELECT 
                        value 
                    FROM 
                        prices 
                    WHERE 
                        prices.id = p.id AND 
                        prices.country='US' AND 
                        prices.timestamp < p.timestamp
                    ORDER BY 
                        prices.timestamp DESC 
                    LIMIT 1) AS last_value
                FROM
                    prices AS p
                INNER JOIN
                    titles AS t
                ON
                    t.row_id = p.id
                WHERE
                    p.country = 'US' AND
                    (SELECT 
                        value 
                    FROM 
                        prices 
                    WHERE 
                        prices.id = p.id AND 
                        prices.country='US' AND 
                        prices.timestamp < p.timestamp
                    ORDER BY 
                        prices.timestamp DESC 
                    LIMIT 1) IS NOT NULL
                GROUP BY
                    t.id
                ORDER BY
                    p.timestamp DESC,
                    last_value DESC
                LIMIT 0, 25"

The first issue I've run into is that, while this query works, titles appear multiple times in the same listing. While this is expected, I'd ideally like only the latest price change to be displayed for the title. To solve this, I tried GROUPING by the titles 'id' (note the: GROUP BY t.id above). Unfortunately, while I'd expect the GROUP to respect the ORDER BY (which orders the latest price changes in DESC order), the results seem to remove the latest changes and show the GROUP'd titles with earlier price values.

Secondly, is there any better way to grab the last price of each item (currently I grab the current value, and then run a subquery to grab the 'last_value' - which effectively represents the value before the current price change). At the moment I run two subqueries: one to grab the second to last known price, and again to ensure that a previous price exists in the database (otherwise there's no point in listing the title as having a price change).

Any help would be appreciated!

Answer 1

How about this:

SELECT titles.id, titles.name, titles.type, prices.value, MAX(prices.timestamp)
FROM titles, prices
WHERE prices.row_id = titles.id AND prices.country='US';

Mind you, I don't have MySQL installed so I couldn't try this query.

[Edit:] I think it won't work 'cause it'll always display the last price entered for all the items because it'll always choose the highest timestamp from the prices table, maybe a group by will do, I'm really sleepy now and I can't think straight;

[Edit2:] How about this: (SELECT max(report_run_date) as maxdate, report_name FROM report_history GROUP BY report_name) maxresults

SELECT titles.id, titles.name, titles.type, prices.value,
    (SELECT MAX(prices.timestamp) as maxtimestamp FROM prices GROUP BY prices.row_id)
FROM titles, prices
WHERE prices.row_id = titles.id AND prices.country='US';