Solved

SQL Help - SELECT most recently sold products

Posted on 2008-10-20
4
348 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:expert-ad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 22763060
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

0
 
LVL 18

Accepted Solution

by:
mdougan earned 250 total points
ID: 22763080
SELECT Top 4 DISTINCT
      op.Product_Id,
       p.name,
      MAX(o.Date_Purchased) as Date_Purchased
FROM
      Orders o
      JOIN Ordered_Products op ON o.Order_Id = op.Order_Id
      JOIN Products P on op.Product_id = P.Product_id
WHERE
      o.Date_Cancelled IS NULL
group by
      op.Product_Id,
       p.name
ORDER BY
      3 DESC
0
 
LVL 18

Expert Comment

by:mdougan
ID: 22763095
Maybe it is SELECT DISTINCT TOP 4 rather than SELECT TOP 4 DISTINCT

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

Author Closing Comment

by:expert-ad
ID: 31508076
Thank you!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question