Returning Distinct Records from a Join

• ch1976

  Valued Member

  Points: 57

  More actions

  August 20, 2003 at 7:17 am

  #162341

  I am often faced with the problem of neeeding to return distinct records from a JOIN where I have multiple matching results. The only way I have come up with solving this problem is using sub queries, which is very very slow. My situation is as below:

  I have a table with data about a customer's product such as this

  ProductIDProductName

  1widget1

  2widget2

  3widget3

  I have a second table with attributes of this product that are constantly being updated,

  but we need to keep the history. We do this by using an effective date to determine which

  is the most current:

  ProductIDAttributeAttributeTypeEffectiveDate

  1brokenstatus1/1/2003

  1fixedstatus4/1/2003

  1in shopstatus6/1/2003

  1brokenstatus8/1/2003

  2brokenstatus1/1/2003

  2fixedstatus4/1/2003

  2in shopstatus6/1/2003

  2brokenstatus8/1/2003

  The only way I have been able to return a distinct result of all the most current info for an attribute type is:

  Select ProductID, Attribute

  From ProductAttributes

  Where AttributeType = 'status'

  AND EffectiveDate =

  (SELECT MAX (EffectiveDate)

  FROM ProductAttributes pa

  WHERE ProductID = ProductAttributes.ProductID

  AND AttributeType = 'status'

  )

  Any ideas on making this more efficient?

  Thanks

• jpipes

  SSChampion

  Points: 12230

  More actions

  August 20, 2003 at 11:10 am

  #470637

  You can use a derived table:

  
  
  SELECT ProductID, Attribute AS "Current Status"
  
  FROM ProductAttribute main
  
  INNER JOIN
  
  (
  
    SELECT ProductID, MAX(EffectiveDate) AS "MaxEffective"
  
    FROM ProductAttributes 
  
    WHERE AttributeType = 'status'
  
    GROUP BY ProductID
  
  ) AS dt
  
    ON main.ProductID = dt.ProductID
  
    AND main.EffectiveDate = dt.MaxEffective
  
  ORDER BY ProductID