title: Distinct across similar records in SQL Server 2008 database
body: <p>I have a SQL Server 2008 database. This database has a table called Product, Order, and OrderProduct. These three tables look like the following:</p>
  
  <pre><code>Product
  -------
  ID
  Name
  Description
  
  Order
  -----
  ID
  OrderDate
  Status
  
  OrderProduct
  ------------
  OrderID
  ProductID
  Quantity
  </code></pre>
  
  <p>I am trying to identify the last three <em>unique</em> products a person ordered. However, I also need to include the last date on which the product was ordered. My problem is I keep getting a result set like this:</p>
  
  <p>Can of Beans (10/10/2011)
  Soda (10/09/2011)
  Can of Beans (10/08/2011)</p>
  
  <p>The second "Can of Beans" should not be there because I already showed "Can of Beans". My query looks like this:</p>
  
  <pre><code>SELECT TOP 3 DISTINCT
    p.[Name],
    o.[OrderDate]
  FROM
    [Product] p,
    [Order] o
    [OrderProduct] l
  WHERE
    l.[ProductID]=p.[ID] and
    l.[OrderID]=o.[ID]
  ORDER BY
    o.[OrderDate] DESC
  </code></pre>
  
  <p>I understand that the reason DISTINCT won't work is because of the order dates are different. However, I'm not sure how to remedy this. Can somebody tell me how to fix this?</p>