MySQL sub-SELECT from same table

mysql

Solution

I think this is a good start for you (if I understand your schema correctly). In English, this will select all SKU's whose quantities yesterday are less than the minimum quantity from all previous days for the same SKU.

SELECT 
    SKU, 
    Quantity

FROM 
    INVENTORY inv

WHERE
    inv.DATE = GETDATE() - 1
    AND inv.QUANTITY < (SELECT MIN(prev_inv.QUANTITY) 
                        FROM INVENTORY prev_inv
                        WHERE 
                            prev_inv.DATE < GETDATE() - 1 
                            AND prev_inv.SKU = inv.SKU
                       )

Problem

This is oversimplified for the purpose of this question, but suppose I have an `INVENTORY` table with the following columns: `SKU`, `DATE`, `QUANTITY`. I want to select all of the columns where the `QUANTITY` from the day previous is less than the `QUANTITY` for any day. How do you write a `SELECT` query with a sub `SELECT` on the same table? Or, if anyone knows what to call this kind of query so I can do more research on my own that would be helpful. (ie., Is this a recursive query?)

Original source