Top 6 ordered items - 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)

Top 6 ordered items

Author

Topic

Darrylw99
Starting Member

6 Posts

Posted - 2009-07-21 : 01:33:06

I need to produce a list of the top 6 items for a given product and when there are the same amount order it again by the brand.

I have a product table (productid), orders and an OrderList table with productId, brand. So for an order that contains the productid 7 i want to return a list of the top 6 products that were purchased when it was

eg: If i order a bath and i also order taps or i order plug then i want a list of the top 6 items i ordered when i bought the bath.

there are other sorting requirements but i cant remember them.

I'm thinking of something like:
SELECT productId, count(productId) as n, brand
FROM OrderList
Where ProductId=2999
Group by productId, brand
Having ProductId=2999
Order by n desc, brand

There might need to be some nifty tsql in there to sort by the brand i'm not sure yet.

Any help greatly appreciated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 01:36:10

I don't have northwind with me, but looking at your query, it will only return 1 record.
Maybe you want something like this, top 6 product by order ?


select *
from
(
    SELECT productId, count(productId) as n, brand, 
           row_no = row_number() over (partition by productId,  brand order by count(productId) desc)
    FROM  OrderList
    Where ProductId=2999
    Where OrderID = ???
    Group by productId, brand
    Having ProductId=2999
)
where row_no <= 6

KH
[spoiler]Time is always against us[/spoiler]

Darrylw99
Starting Member

6 Posts

Posted - 2009-07-21 : 01:51:37

Cheers but i cant get that to work in sql server 2005. Its sad but i'm at home thinking of a work problem. so i'm not using their database.
If we use Northwind and the Order Details table there are 38 rows for ProductId=1 so for each of those Orders List the top 6 items that were bought.

I've never come across the Partition command before and when i run it it errors at the Where clause.

Sorry to be a pain.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 02:00:52

further reference on row_number()
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 02:07:18

quote:
Originally posted by Darrylw99

Cheers but i cant get that to work in sql server 2005. Its sad but i'm at home thinking of a work problem. so i'm not using their database.
If we use Northwind and the Order Details table there are 38 rows for ProductId=1 so for each of those Orders List the top 6 items that were bought.

I've never come across the Partition command before and when i run it it errors at the Where clause.

Sorry to be a pain.

I don't have Northwind, but it should be something like this.


select *
from
(
    SELECT OrderID, count(productId) as n,
           row_no = row_number() over (partition by OrderID order by count(productId) desc)
    FROM  OrderList
    Where ProductId = 1
    Group by OrderID
)
where row_no <= 6

northwind ? are you using SQL 2000 or 2005 ? row_number() only works on 2005 / 2008

KH
[spoiler]Time is always against us[/spoiler]

Darrylw99
Starting Member

6 Posts

Posted - 2009-07-21 : 02:14:36

2005 i've tried the example but it doesnt return the top 6 and there count for a given productId. it just returns productId, n and row_no.

I need it to return the list of items that were bought when a product was also bought. this only returns me my productid and the number of times it exists. I need to return all the other items grouped and counted that were ordered when i also ordered this product.

Hope i explained it properly.

I do appreciate your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-21 : 03:27:16

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Darrylw99
Starting Member

6 Posts

Posted - 2009-07-21 : 04:02:38

I'm at work now, so here goes more detail:
I have a Products table (productId) and ProductBrand
Orders table (OrderId)
OrderItems table (OrderItemID int auto, OrderId, ProductId)

I need to List the Top 6 items for a given productId that were ordered at the same time as this product. Order by the nbumber of times it was ordered then when the same number of times order by Brand and then by Most recent date it was ordered.

This is a little more complex than i'm used to, so any help is hugely appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources