viljun viljun - 2 months ago 7
SQL Question

How to get the last modified rows from the product inventory

I have a product inventory log table (see below). Every time a product's stock quantity is changed, a row with its current stock amount is added to the table.

So...


  • How can I know how much and what products was in the inventory at the specified time?

  • Is it possible know this with one query?

  • How about without joins or subqueries?



Log table:

row_id quantity time product_id
2413407 1 2016-09-16 13:16:48 122559
2413408 3696 2016-09-16 20:46:42 121152
2413409 3697 2016-09-16 20:46:45 121152
2413410 786 2016-09-16 20:46:47 121152
2413411 3 2016-09-16 20:53:19 128034
2413412 1 2016-09-16 20:53:20 143362
2413413 2 2016-09-16 20:53:30 128051


For example using timestamp 2016-09-16 20:46:46 I'd like to get out these rows (because this was the exact inventory at that moment):

row_id quantity time product_id
2413407 1 2016-09-16 13:16:48 122559
2413409 3697 2016-09-16 20:46:45 121152

Answer

You are going to need subqueries or joins. Here is one way:

select l.*
from log l
where l.time = (select max(l2.time)
                from log l2
                where l2.product_id = l.product_id and
                      l2.time <= '2016-09-16 20:46:46'
               );

I should not have said that. There is at least one way to get this without join or subqueries:

select product_id,
       substring_index(group_concat(row_id order by time desc), ',', 1) as row_id,
       substring_index(group_concat(quantity order by time desc), ',', 1) as quantity,
       max(time)
from log l
where l.time <= '2016-09-16 20:46:46'
group by product_id;

This method is a bit dangerous because the intermediate group_concat() string is limited to 1,024 bytes. This is a session setting that can easily be changed. In addition, this turns the other columns (quantity and row_id) into strings.

Finally, I think the original version is faster with the right index log(product_id, time). But it is possible to meet your conditions.