Top Row in an Outer Join?

[sm5574]

Using a stored procedure on SQL Server.

 

Here are my tables:

 

Families

Groups

Lines

SubLines

Parts

PartSubLines

PartPrices

 

Each Group belongs to a Family, each Line to a Group, each SubLine to a Line, and each Part to a SubLine (many-to-many through PartSubLines).

 

There is a 1-to-1 relationship between Parts and PartPrices. They share the PartID field.

 

Here's the thing: There can be more than one price for each part. This allows us to track price history. So there is a unique date with each price.

 

I need to pull every part, including ONLY the most recent date. My thought is to pull a TOP 1 and ORDER BY Date DESC. But I can't figure out how to do that.

 

So far I have been using a Left Outer Join, but that isn't giving me what I need. Here's what I have:

 

SELECT Parts.PartID AS PartID,

  Parts.PartNumber AS PartNumber,

  Parts.Description AS Description,

  PartPrices.Price AS Price

  FROM Parts

  LEFT OUTER JOIN PartPrices ON PartPrices.PartID = Parts.PartID

  PartSublines, Lines, Groups, Families, SubLines

      WHERE Groups.FamilyID = Families.FamilyID

  AND Lines.GroupID = Groups.GroupID

  AND SubLines.LineID = Lines.LineID

  AND SubLines.Obsolete = 0

  AND PartSubLines.SubLineID = SubLines.SubLineID

  AND Parts.PartID = PartSubLines.PartID

 

Thank you!

[sm5574]

Sorry, I can't figure out how to modify my post...

 

I should have said, there is a 1-to-many relationship between Parts and PartPrices.