MySQL Select latest stock count records

Hi!

You can do it like this.

Select dl.area, dl.name as location, si.id, si.name as item_ref, ssi.Updated_at,ssi.count
From data_location as dl
inner join stk_stocktake_header ssh on dl.id = ssh.location_id
inner join stk_stocktake_item ssi on ssi.stocktake_header_id = ssh.id
inner join stk_item as si on si.id = ssi.stk_item_id
inner join stk_product as sp on si.stk_product_id = sp.id
inner join stk_stock_ref as ssr on sp.stk_stock_ref_id = ssr.id
Where dl.area in ('Area 1 Scotland')
and dl.name in ('Callander - 030')
and si.id in (11391,11392)
and ssi.Updated_at = (select max(ssi2.Updated_at) from stk_stocktake_item ssi2 where ssi2.stocktake_header_id = ssi.stocktake_header_id and ssi2.stk_item_id = ssi.stk_item_id)
order by si.id,ssi.Updated_at desc

Select allOpen in new window

Hope this helps.

Regards,
    Tomas Helgi

Hi Tomas
I tried your SQL but it selected the same records my SQL code selected and not the two records I wanted.

Regards

SQLSearcher

What's the unique key in stk_stocktake_header?

Hi!

Try this

Select dl.area, dl.name as location, si.id, si.name as item_ref, ssi.Updated_at,ssi.count
From data_location as dl
inner join stk_stocktake_header ssh on dl.id = ssh.location_id
inner join stk_stocktake_item ssi on ssi.stocktake_header_id = ssh.id
inner join stk_item as si on si.id = ssi.stk_item_id
inner join stk_product as sp on si.stk_product_id = sp.id
inner join stk_stock_ref as ssr on sp.stk_stock_ref_id = ssr.id
Where dl.area in ('Area 1 Scotland')
and dl.name in ('Callander - 030')
and si.id in (11391,11392)
and ssi.Updated_at = (select max(ssi2.Updated_at) from stk_stocktake_item ssi2 where ssi2.stocktake_header_id = ssi.stocktake_header_id )
order by si.id,ssi.Updated_at desc

Select allOpen in new window

Or this

Select dl.area, dl.name as location, si.id, si.name as item_ref, ssi.Updated_at,ssi.count
From data_location as dl
inner join stk_stocktake_header ssh on dl.id = ssh.location_id
inner join stk_stocktake_item ssi on ssi.stocktake_header_id = ssh.id
inner join stk_item as si on si.id = ssi.stk_item_id
inner join stk_product as sp on si.stk_product_id = sp.id
inner join stk_stock_ref as ssr on sp.stk_stock_ref_id = ssr.id
Where dl.area in ('Area 1 Scotland')
and dl.name in ('Callander - 030')
and si.id in (11391,11392)
and ssi.Updated_at = (select max(ssi2.Updated_at) from stk_stocktake_item ssi2 where ssi2.stk_item_id = ssi.stk_item_id)
order by si.id,ssi.Updated_at desc

Select allOpen in new window

Regards,
    Tomas Helgi

Hello
The Unique key in stk_stocktake_header is id.

Tomas your SQL got better but did not resolve the issue it brought back 4 records 2 for each item.

Regards

SQLSearcher

Try my query above.  Though it is essentially doing the same as Tomas' 2nd query.  So if mine works, his should too. At least I think so, without having run it..

Hi!

Try this

Select dl.area, dl.name as location, si.id, si.name as item_ref, ssi.Updated_at,ssi.count
From data_location as dl
inner join stk_stocktake_header ssh on dl.id = ssh.location_id
inner join stk_stocktake_item ssi on ssi.stocktake_header_id = ssh.id
inner join stk_item as si on si.id = ssi.stk_item_id
inner join stk_product as sp on si.stk_product_id = sp.id
inner join stk_stock_ref as ssr on sp.stk_stock_ref_id = ssr.id
Where dl.area in ('Area 1 Scotland')
and dl.name in ('Callander - 030')
and si.id in (11391,11392)
and ssi.Updated_at = (select max(ssi2.Updated_at) from stk_stocktake_item ssi2 where ssi2.stk_item_id = ssh.id)
order by si.id,ssi.Updated_at desc

Select allOpen in new window

_agx_ your derived table would search all data from the table then do filtering on max instead of filtering out just the needed id's in the stk_stocktake_item table.

Regards,
     Tomas Helgi

Thank you for your help.

The SQL Script I ended up with looked like this;

SELECT
            dl.area
            , dl.name as location
            , si.ref as item_ref
            , si.name as item_name
            , ssi.count as qty
 FROM  data_location as dl
       INNER JOIN stk_stocktake_header ssh ON dl.id = ssh.location_id
       INNER JOIN stk_stocktake_item ssi ON ssi.stocktake_header_id = ssh.id
       INNER JOIN stk_item as si ON si.id = ssi.stk_item_id
       INNER JOIN (
            SELECT ssh.location_id, stk_item_id, MAX(ssi.Updated_at) AS Updated_at
              FROM   stk_stocktake_item ssi
                        Inner join stk_stocktake_header ssh ON ssi.stocktake_header_id = ssh.id
                        INNER JOIN data_location as dl ON dl.id = ssh.location_id
                        where stk_item_id in (11391,11392)
                        and dl.area in ('Area 1 Scotland','Area 7 M25 & South')
                        AND dl.name in ('Callander - 030','Harrow - 063')
              GROUP BY ssh.location_id,stk_item_id
       ) mx
       On mx.stk_item_id = ssi.stk_item_id
            AND mx.Updated_at = ssi.Updated_at
            AND mx.location_id = ssh.location_id
       INNER JOIN stk_product as sp ON si.stk_product_id = sp.id
       INNER JOIN stk_stock_ref as ssr ON sp.stk_stock_ref_id = ssr.id
 WHERE    dl.area in ('Area 1 Scotland','Area 7 M25 & South')
 AND dl.name in ('Callander - 030','Harrow - 063')
 AND      si.id in (11391,11392)
 ORDER BY si.id, ssi.Updated_at desc

Regards

SQLSearcher

@Tomas Helgi - True.  The net effect should be the same, but you'd have to look at the execution plan to see which is more efficient. It's quite possible the subquery approach would be more efficient than mine.

@SQLSearcher - Since you're already filtering in the derived query, you shouldn't need to do it again in the outer WHERE clause.  It won't hurt anything, but it's redundant.