Solved

MySQL Select latest stock count records

Posted on 2016-10-27
11
74 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 …
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…

856 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