ECommerce query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ECommerce query

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2009-01-28 : 12:11:38

Select 3 distinct products ordered with the orderdate in desc ie. recently

I wrote the following query but it is returning duplicate productid

select DISTINCT top 3
b.productid,
a.OrderDate,
c.productname
from orders as a
inner join orderdetails as b on a.orderid = b.orderid
inner join products as c on b.productid = c.productid
order by a.orderdate desc

productid OrderDate productname
----------- ----------------------- --------------------------------------------------
2 3004-04-05 00:00:00.000 Product Name 2
1 2009-01-01 00:00:00.000 Product Name 1
2 2009-01-01 00:00:00.000 Product Name 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 12:27:14

did you mean this?


select top 3 with ties
b.productid,
a.OrderDate,
c.productname,
MAX(OrderDate) OVER (PARTITION BY productid) AS Latest
from orders as a
inner join orderdetails as b on a.orderid = b.orderid
inner join products as c on b.productid = c.productid
order by Latest desc

mapidea
Posting Yak Master

124 Posts

Posted - 2009-01-29 : 09:06:16

Thanks Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 09:15:36

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources