asked on

SQL Help - SELECT most recently sold products

Hiya,

I'm hoping that someone can give me a hand with the following SQL problem:

The database contains the following tables:-

Orders
----------
Order_Id
Date_Purchased
...

Products
------------
Product_Id
Name
..

Ordered_Products
-----------------------
Order_Id
Product_Id

All I want to do is select the Product_Id and Name of the 4 most recently sold products without the risk of the list containing duplicate products.

This SQL...

SELECT DISTINCT
      op.Product_Id,
      o.Date_Purchased
FROM
      Orders o
      JOIN Ordered_Products op ON o.Order_Id = op.Order_Id
WHERE
      o.Date_Cancelled IS NULL
ORDER BY
      o.Date_Purchase DESC

.. doesn't select unique products due to the "Date_Purchased", the output of the above SQL statement is:

Product_Id Date_Purchased
----------- -----------------------
21 2008-09-13 20:03:44.220
52 2008-09-13 20:03:44.220
37 2008-09-10 21:11:01.127
46 2008-09-10 21:01:36.593
46 2008-09-09 20:43:05.063

Thanks in advance for any help.

Ad

hielo

try:

SELECT DISTINCT TOP 4
      op.Product_Id,
      o.Date_Purchased
FROM
 Orders as o, Products as p, Ordered_Products as op
WHERE 
      o.Order_id=op.Order_id
AND 
      p.Product_Id = op.Product_Id
AND 
      o.Date_Cancelled IS NULL
ORDER BY
      o.Date_Purchase DESC

Open in new window

ASKER CERTIFIED SOLUTION

mdougan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mdougan

Maybe it is SELECT DISTINCT TOP 4 rather than SELECT TOP 4 DISTINCT

But, you do have to group by the Product, Product Name

expert-ad

ASKER

Thank you!