?
Solved

SQL Help - SELECT most recently sold products

Posted on 2008-10-20
4
Medium Priority
?
355 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 1000 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.
Suggested Courses

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