MYSQL query to return most recent entry regardless of date entered

I am really bad explaining but I'll try. Suppose I have with the following tables:

CREATE TABLE inventory (
    Store VARCHAR(16),
    Product_Code INT UNSIGNED,
    Inventory_Qty INT UNSIGNED,
    `Date` DATE
);

CREATE TABLE Alias (
    computername VARCHAR(16),
    storename VARCHAR(32),
);

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 s开发者_如何学Pythontores:

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

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.

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 

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.