Solved

SQL Help - SELECT most recently sold products

Posted on 2008-10-20
4
345 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
  • 2
4 Comments
 
LVL 82

Expert Comment

by:hielo
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now