Distinct across similar records in SQL Server 2008 database

I have a SQL Server 2008 database. This database has a table called Product, Order, and OrderProduct. These three tables look like the following:

Product
-------
ID
Name
Description

Order
-----
ID
OrderDate
Status

OrderProduct
------------
OrderID
ProductID
Quantity

I am trying to identify the last three unique products 开发者_C百科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:

Can of Beans (10/10/2011) Soda (10/09/2011) Can of Beans (10/08/2011)

The second "Can of Beans" should not be there because I already showed "Can of Beans". My query looks like this:

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

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?

---

WITH cteProducts AS (
    SELECT p.Name, o.OrderDate,
           ROW_NUMBER() OVER(PARTITION BY p.Name ORDER BY o.OrderDate DESC) as RowNum
        FROM Product p
            INNER JOIN OrderProduct op
                ON p.ID = op.ProductID
            INNER JOIN Order o
                ON op.OrderID = o.ID
)
SELECT TOP 3 Name, OrderDate
    FROM cteProducts
    WHERE RowNum = 1
    ORDER BY OrderDate DESC;

---

Have you tried GROUP BY?

SELECT TOP 3
  p.[Name],
  max(o.[OrderDate])
FROM
  [Product] p,
  [Order] o
  [OrderProduct] l
WHERE
  l.[ProductID]=p.[ID] and
  l.[OrderID]=o.[ID]
GROUP BY p.[Name]
ORDER BY
  max(o.[OrderDate]) DESC

---

Try grouping like :

SELECT TOP 3
  p.[Name], 
  MAX(o.[OrderDate]) 
FROM 
  [Product] p, 
  [Order] o 
  [OrderProduct] l 
WHERE 
  l.[ProductID]=p.[ID] and 
  l.[OrderID]=o.[ID] 
GROUP BY p.[Name] 
ORDER BY 
  MAX(o.[OrderDate]) DESC 

---

Use GROUP BY... it's been a while since I've used SQL Server, but the query will look something like this:

SELECT TOP 3
    p.[Name],
    max(o.[OrderDate]) AS MostRecentOrderDate
FROM
    [Product] p,
    [Order] o
    [OrderProduct] l
WHERE
    l.[ProductID]=p.[ID] and
    l.[OrderID]=o.[ID]
GROUP BY p.[Name]
ORDER BY
    MostRecentOrderDate DESC

Or to show the first time they ordered that product, choose min() instead of max()