Solved

MySQL Select latest stock count records

Posted on 2016-10-27
11
36 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
  • 5
  • 3
  • 3
11 Comments
 
LVL 24

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
 
LVL 24

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 24

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now