Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Help - SELECT most recently sold products

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

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

580 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