Help With SQL Query

[shragel]

I have a inventory Table with a list of item numbers.

I also have a sales table showing all sales. The following field are in the sales table

Quantity
DateSold
ItemNum
CustomerName


I need a to show all the items of the inventory table together with the last record of the sales table. So for each item a only need the last sale.

How can i create such a query?

Thanks for your help.

[robbedaya]

something like this?
SELECT Inventory.Description, Max(Sales.DateSOld) AS MaxVanDateSOld, Max(Sales.ItemNUm) AS MaxVanItemNUm, Max(Sales.Customername) AS MaxVanCustomername
FROM Inventory INNER JOIN Sales ON Inventory.ID = Sales.ItemNUm
GROUP BY Inventory.Description;

I've created this query on a MS Access DB. Because you didn't mention wich type you were using. I think it should do the trick.

Next time ask databasequestions here

[szlamany]

This will do it:

Select * From SomeTable S1
Where S1.DateSold=(Select Max(S2.DateSold) From SomeTAble S2
                         Where S2.ItemNum=S1.ItemNum)

Basically only selecting the rows where the DateSold is the max DateSold for that item.

[shragel]

Thanks all

robbedaya:

the problem is it returns the biggest customer name Max(customername) even if it isn't the same as the max(datesold)

szlamany:

What happens when two people ordered the same day? I only want one of them returned.

I am using MS Access.

[szlamany]

szlamany:

What happens when two people ordered the same day? I only want one of them returned.

Well - tell me which one you want returned if two customers order.

Does anything indicate the "latest" one in the day? Do you simply want the "latest" alphabetical one?

[shragel]

The "latest" alphabetical one?

[szlamany]

Well - that does make it a bit more complex.

Maybe it's best to create two views so that it's easier to see how this is going to work. You could always put the two views into "derived" tables like my first example - but basically it's going to work the same either way.

These two views give you the hook to the row wanted.

Create View MaxDateSold
   As Select ItemNum,Max(DateSold) "MaxDateSold"
         From SomeTable

Create View LastDateCust
   As Select MD.ItemNum,MD.MaxDateSold,Max(S1.CustomerName)
         From MaxDateSold MD
         Left Join SomeTable S1 on S1.ItemNum=MD.ItemNum
                                and S1.DateSold=MD.MaxDateSold

The second view - LastDateCust - should be what you JOIN to first.

Select * From ...
  Left Join LastDateCust LD on LD.ItemNum=... and LD.MaxDateSold=... and LD.CustomerName=...
  Left Join ...