Conditional logic on SQL aliases

Question

I've got an SQL query that pieces together historic price information. It's not particularly clean and could probably be optimized a great deal. One reason for this is that I use a subquery to grab the 'previous' price value via a subquery and assign it to the alias 'last_value'. Ideally, I'd like to be able to use this alias in the queries WHERE clause, rather than having to re-perform the lookup.

I understand why this is not possible (SQL queries are parsed from right to left, with aliases applied before the data is piped back, as far as I understand it). I just wanted to post here to ask if something like this can be made possible using alternative means.

Specifically, I'd quite like to calculate the percentage difference between p.value (the current value) and last_value (the last value) via SQL, rather than having to do it at the application layer.

I've considered storing the percentage difference as a separate column in the table itself, but this seems a little backwards (given that the logic involved is trivial, and the only stumbling block is the use of the alias).

I'm using MySQL.

$sql = "SELECT 
                t.*,
                p.value,
                p.timestamp,
                (SELECT 
                    value 
                FROM 
                    prices 
                WHERE 
                    prices.id = p.id AND 
                    prices.country=':country' AND 
                    prices.timestamp < p.timestamp
                ORDER BY 
                    prices.timestamp DESC 
                LIMIT 1) AS last_value
            FROM
                prices AS p,
                titles AS t
            WHERE
                t.row_id = p.id AND
                p.country = ':country' AND
                p.current = 1 AND
                (SELECT 
                    value 
                FROM 
                    prices 
                WHERE 
                    prices.id = p.id AND 
                    prices.country=':country' AND 
                    prices.timestamp < p.timestamp
                ORDER BY 
                    prices.timestamp DESC 
                LIMIT 1) IS NOT NULL
            ORDER BY
                p.timestamp DESC
            LIMIT :offset, :limit";

Answer 1

I never actually used MySQL, but in SQL Server I would do something like this:

SELECT (last.value - current.value) / last.value AS percentage
FROM (SELECT .... ) AS last
INNER JOIN (SELECT ...) AS current
ON last.PK = current.PK

It might still work in MySQL, as it's pretty much standad SQL-92, I believe. Here's a link to MySQL docs on this: http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html

Answer 2

Alexander had the right idea (I unfortunately cannot up vote you because I have since signed up with a proper Stackoverflow account). The SQL I ended up using:

SELECT
                    current.*,
                    last.value AS last_value
                FROM
                    (SELECT
                        t.*,
                        p.id AS p_id,
                        p.value,
                        p.timestamp
                    FROM
                        prices AS p,
                        titles AS t
                    WHERE
                        t.row_id = p.id AND
                        p.country = ':country' AND
                        p.current = 1
                    ) current
                INNER JOIN
                    (SELECT 
                        prices.* 
                    FROM 
                        prices 
                    WHERE 
                        prices.country = ':country' AND
                        prices.current = 0
                    ORDER BY
                        prices.timestamp DESC) last 
                    ON 
                        last.id = current.p_id AND 
                        last.timestamp < current.timestamp
                GROUP BY
                    current.row_id
                ORDER BY
                    current.timestamp DESC,
                    current.name ASC
                LIMIT :offset, :limit