MySQL sub-SELECT from same table

Question

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?)

Your result set is empty, because a quantity can't be less than the minimum of a set of which it is a member... — Keith Randall
– Keith Randall, Commented May 23, 2012 at 1:48
@KeithRandall -- why not? If I sold zero phones yesterday, but 10 phones on each other day before that, that's a minimum quantity that he wants to select. — BeemerGuy
– BeemerGuy, Commented May 23, 2012 at 1:54
@beemerGuy.net: I'm being pedantic, but if you sold 10 phones one day and 0 phones the other, then the minimum quantity for any day is 0. My comment was something of a joke, in that the OP intended to say "any day before the previous day", not just "any day" (which includes the previous day). — Keith Randall
– Keith Randall, Commented May 23, 2012 at 2:00
@KeithRandall -- that is pedantic. Contextually, I'm sure he meant "any other day". — BeemerGuy
– BeemerGuy, Commented May 23, 2012 at 2:04

BeemerGuy · Accepted Answer · 2012-05-23 01:52:51Z

3

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
                       )

answered May 23, 2012 at 1:52

BeemerGuy

8,2892 gold badges39 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

psyklopz Over a year ago

This is helpful, I didn't think to use table aliases. I'm not sure if you are willing to correct this, but I need the sku, quantity, and date where the quantity of the immediate day before was less.

BeemerGuy Over a year ago

wait.... you want to compare it to the previous day? Or any previous day? @KeithRandall deserves credit for being pedantic after all =)

JBS Over a year ago

I found that a query like this takes long to execute (>11 secs in my case). Any way to make it faster?

Andrew · Accepted Answer · 2012-05-23 02:05:41Z

3

You can join a table to its self for this sort of thing. The exact syntax probably isn't right for the date math and if you have multiple records per day you might want to do some GROUPing, but I hope you get the idea.

SELECT i1.*
FROM INVENTORY AS i1
JOIN INVENTORY AS i2 ON i2.DATE = i1.DATE + 1
WHERE i1.QUANTITY > i2.QUANTITY

answered May 23, 2012 at 2:05

Andrew

4,62429 silver badges31 bronze badges

1 Comment

BoogaBooga Over a year ago

you sir are an absolute gentleman and a scholar, this makes so much sense and is very readable and im 10 years too late but still

Collectives™ on Stack Overflow

MySQL sub-SELECT from same table

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related