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.
The second view - LastDateCust - should be what you JOIN to first.Code: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
Code:Select * From ... Left Join LastDateCust LD on LD.ItemNum=... and LD.MaxDateSold=... and LD.CustomerName=... Left Join ...




Reply With Quote