mysql Retrieve and manipulate current row and previous row from table

Question

I have looked several places, and maybe I'm just not phrasing the search correctly. I have found similar questions, but none of them answer the question.

I have a table of Sight Inventories (where users walk through the storage area and physically check how many products are on hand). The table handles multiple locations. The table structure (partial, only the information needed) is:

create table location_inventory (
    id                int unsigned not null auto_increment,
    location_id       int unsigned references location(location_id),
    inventory_item_id int unsigned references inventory_item (inventory_item_id),
    inventory_date    date comment 'Date the sight inventory was taken',
    quantity          decimal( 15,2 ) comment 'Number of items found during inventory',
    primary key ( id ),
    unique            (location_id,inventory_item_id,inventory_date)
);

It should be a query of the form:

select
    a.location_id location,
    a.inventory_item_id inventory_item,
    a.inventory_date curr_date,
    a.quantity curr_quant,
    b.inventory_date prev_date,
    b.quantity prev_quant,
    a.quantity - b.quantity num_used
from
    location_inventory a
    left outer join
       (
         select
            location_id,
            inventory_item_id,
            inventory_date,
            quantity
          from
            location_inventory
          where
           something
      ) b
      on ( location_id,inventory_item_id )
  where
      a.inventory_date between DATEA and DATEB

But I haven't gotten it to work.

It is that whole subquery that I'm missing. I've seen several answers where we get the previous date, but none where I can actually retrieve the rest of the values from the previous row; it ends up retrieving the values for the most recent entry in the entire table.

MySQL 8.0.12 is now GA and has inbuilt solution for this see `lag()` here https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html — Paul Maxwell, Sep 24 '18 at 23:13
Sorry, I left out some housekeeping things and did not include the primary key which, I definitely use. I edited the question and added that column and the primary key definition. — Rod, Sep 25 '18 at 02:50
Thanks, it then then my first option is viable. Have you tried any of these yet> — Paul Maxwell, Sep 25 '18 at 02:54
I prefer the lag() function (didn't know it existed), but it is only available in MariaDB 10.2. I upgraded to Devuan ASCII, but it was only Maria 10.1. — Rod, Sep 25 '18 at 03:09

Paul Maxwell · Accepted Answer · 2018-09-25T03:08:53.217

When you choose to only reveal part of a table structure you can omit things we may need. Below I have assumed you have a column id as a unique identifier for each row

SELECT
    a.location_id           location
  , a.inventory_item_id     inventory_item
  , a.inventory_date        curr_date
  , a.quantity              curr_quant
  , b.inventory_date        prev_date
  , b.quantity              prev_quant
  , a.quantity - b.quantity num_used
FROM (
    SELECT
        *
      , (SELECT id
         FROM location_inventory
         WHERE location_id = t.location_id
           and inventory_item_id = t.inventory_item_id
           and inventory_date < t.inventory_date
         ORDER BY inventory_date DESC
         LIMIT 1
        ) prev_id
    FROM location_inventory t
    ) a
LEFT OUTER JOIN location_inventory b ON a.prev_id = b.id
WHERE a.inventory_date BETWEEN DATEA AND DATEB

Another method would be to use correlated subqueries for each of the wanted values:

SELECT
    a.location_id           location
  , a.inventory_item_id     inventory_item
  , a.inventory_date        curr_date
  , a.quantity              curr_quant
  , (SELECT inventory_date
         FROM location_inventory
         WHERE location_id = t.location_id and inventory_item_id = t.inventory_item_id and inventory_date < t.inventory_date
         ORDER BY inventory_date DESC
         LIMIT 1
        )                   prev_date
  , (SELECT quantity
         FROM location_inventory
         WHERE location_id = t.location_id and inventory_item_id = t.inventory_item_id and inventory_date < t.inventory_date
         ORDER BY inventory_date DESC
         LIMIT 1
        )                   prev_quant
  , a.quantity 
    - (SELECT quantity
         FROM location_inventory
         WHERE location_id = t.location_id and inventory_item_id = t.inventory_item_id and inventory_date < t.inventory_date
         ORDER BY inventory_date DESC
         LIMIT 1
        )
FROM location_inventory t
WHERE a.inventory_date BETWEEN DATEA AND DATEB

Since release of version 8.0 MySQL supports window functions such as lag() which makes this far easier and more efficient.

SELECT
    a.location_id           location
  , a.inventory_item_id     inventory_item
  , a.inventory_date        curr_date
  , a.quantity              curr_quant
  , lag(inventory_date,1) over(partiton by location_id,inventory_item_id order by inventory_date DESC)  prev_date
  , lag(quantity,1) over(partiton by location_id,inventory_item_id order by inventory_date DESC)        prev_quant
  , a.quantity 
    - lag(quantity,1) over(partiton by location_id,inventory_item_id order by inventory_date DESC)       num_used
FROM location_inventory a
WHERE a.inventory_date BETWEEN DATEA AND DATEB

final note: I am not in favour of aliasing schemes that are dependent on sequence in a query

Thank you. There are a couple of minor errors in your first two examples but, once I fixed them, it appears to work correctly. You missed the t. on the right side of the comparisons in a couple of places, ie in example 1, in the 'select id' subquery, the where clause should be and inventory_item_id = t.inventory_item_id and inventory_date < t.inventory_date. However, it works after fixing those. — Rod, Sep 25 '18 at 03:05
great. glad you worked it out. I edited the answer - good pick-up — Paul Maxwell, Sep 25 '18 at 03:07
For anyone looking here later, I did a little profiling. Very simple, I just turned on profiling and ran each option three times. Option #1 consistently ran over twice as fast as option #2. However, option #2 can be turned into a view. I can not run option #3 on my system but assume it is fast and can be a view. — Rod, Sep 25 '18 at 04:32

mysql Retrieve and manipulate current row and previous row from table

1 Answers1