SQLSearcher
asked on
MySQL Select latest stock count records
Hello Experts Exchange
I have a MySQL query that gives me all the stock count records from the database.
I want to select the latest records for the stock items selected.
I have attached a CSV file of example data.
This is my current query;
The records I want from the example data are;
Area 1 Scotland Callander - 030 11391 PLU3941 Diary-2017 Corporate Pink 2016-10-27 09:09:20 12.00000
Area 1 Scotland Callander - 030 11392 PLU3942 Diary-2017 Corporate Turquoise 2016-10-27 09:09:20 8.00000
How do I change my SQL to only select the latest records for each item?
Regards
SQLSearcher
General_Stock_Output.csv
I have a MySQL query that gives me all the stock count records from the database.
I want to select the latest records for the stock items selected.
I have attached a CSV file of example data.
This is my current query;
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)
order by si.id,ssi.Updated_at desc
The records I want from the example data are;
Area 1 Scotland Callander - 030 11391 PLU3941 Diary-2017 Corporate Pink 2016-10-27 09:09:20 12.00000
Area 1 Scotland Callander - 030 11392 PLU3942 Diary-2017 Corporate Turquoise 2016-10-27 09:09:20 8.00000
How do I change my SQL to only select the latest records for each item?
Regards
SQLSearcher
General_Stock_Output.csv
ASKER
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
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
Or this
Tomas Helgi
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
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
Regards,Tomas Helgi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
_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
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
_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
ASKER
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_i d
) 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
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_i
) 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.
You can do it like this.
Open in new window
Hope this helps.
Regards,
Tomas Helgi