Solved

MySQL Select latest stock count records

Posted on 2016-10-27
11
83 Views
Last Modified: 2016-10-27
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;

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

Open in new window


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
0
Comment
Question by:SQLSearcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 41862001
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

Open in new window


Hope this helps.

Regards,
    Tomas Helgi
0
 

Author Comment

by:SQLSearcher
ID: 41862038
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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41862075
What's the unique key in stk_stocktake_header?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 41862077
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

Open 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

Open in new window

Regards,
    Tomas Helgi
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 41862084
Edit:  Oops, the posts clashed.  Since it was already written, may as well leave it.

Use a derived table to grab the latest "Updated_at" for each "stk_item_id". Then JOIN back to the other tables on those 2 fields. Something 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
      (
           SELECT stk_item_id, MAX(Updated_at) AS Updated_at
             FROM   stk_stocktake_item
             GROUP BY stk_item_id
      ) mx
      INNER JOIN mx.stk_item_id = si.id AND mx.Updated_at = ssi.Updated_at
      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
0
 

Author Comment

by:SQLSearcher
ID: 41862096
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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41862114
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..
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 41862141
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

Open 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
0
 

Author Closing Comment

by:SQLSearcher
ID: 41862200
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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41862300
@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.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41862329
@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.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question