MYSQL query to return most recent entry regardless of date entered

Question

Hello, I am really bad explaining but I'll try...

I want to create a MYSQL query that returns the data based on the last entry in the table. I did it fine with the MAX function based on the date, but I need more advanced in terms of finding the last item entered by a specific product and return the last qty even if it's not the same date. This is a sample of the data I want as result:

Store| Product| Qty| Date
S1 | 1115 | 10| 12/30/09
S2 | 1115 | 20| 12/30/09
S3 | 1115 | 5| 12/29/09
S4 | 1115 | 15| 12/28/09

The query I have is this one, but it is only returning S1 and S2 because I have max on the date, but I want to return the most recent entry of x product by all stores:

Select 
alias.storename,
inventory.Product_Code,
inventory.Inventory_Qty,
inventory.Date
From inventory Inner Join
Alias On inventory.Store = Alias.computername
Where inventory.Date = (Select Max(inventory.Date) From inventory)
Order By Alias.storename,
inventory.Product_Code

Any help is really appreciated

Answer 1

If you want the most recent record entered into the table for each store, then

Select a.storename, 
    i.Product_Code, i.Inventory_Qty, i.Date 
From Alias a 
   left Join inventory i
       On i.Store = a.computername 
          and i.Date = 
             (Select Max(Date) 
              From inventory
              Where Store = i.Store) 
Order By a.storename, i.Product_Code 

Answer 2

Have a look at

SELECT  i.*
FROM    (
            SELECT  Store,
                    Product,
                    MAX(Date) MaxDate
            FROM    inventory
            GROUP BY    Store,
                        Product
        ) MaxDates INNER JOIN
        inventory i ON MaxDates.Store = i.Store
                        AND MaxDates.Product = i.Store
                        AND MaxDates.MaxDate = i.Date

This will firstly get the Max Date per Store per Item and then return that record for which it is the Last entry.

Let me know if I misunderstood your request.

Answer 3

I believe this will get you what you want:

SELECT 
    alias.storename,
    inventory.Product_Code,
    inventory.Inventory_Qty,
    lastdate.date
FROM (
    SELECT 
        store, 
        max(date) as date 
    FROM inventory 
    GROUP BY store
    ) lastdate
INNER JOIN inventory
    ON inventory.store = lastdate.story
        AND inventory.date = lastdate.date
INNER JOIN alias
    ON inventory.store = alias.computername
ORDER BY 
    alias.storename,
    inventory.Product_Code

This will get all the items that each store sold on the last date which that store sold any items.