MySQL if previous row value is greater than current row value

Question

I have a table with 2 columns: date (date) and value(int). How can I write a query to output the date and either 'Up', 'Down', or 'Same' by comparing the previous date's value to the current date's value. (e.g. if 12/01/2016 has a value of 100 and 12/02/2016 has a value of 200 then the output should be 12/02/2016 Up.

Answer 1

What you're looking for here is the SQL LAG() function. This will allow you to retrieve the previous entry for value, and compare to the current entry.

Using CASE, you can then assign "Up", "Down" or "Same", based on the comparison result:

SELECT date,
    CASE 
        WHEN value > LAG(value) OVER (ORDER BY date) THEN 'Up'
        WHEN value < LAG(value) OVER (ORDER BY date) THEN 'Down'
        ELSE 'Same'
    END AS change
FROM 
    table_name;