Post reply

Retrieve top 6 ordered Items

  • July 21, 2009 at 2:10 am

    #155989

    Hi

    I have a Products table (ProductId, ProductBrand)

    Orders table (OrderId)

    OrderItems table (OrderItemId auto, OrderId, ProductId)

    I need to retrieve a list of the top 6 products that were bought for a given product in the same order.

    So if i ordered a bath and i ordered a tap and a plug (several times) I want to group the items bought with the number of times bought

    I also need then to order by brand if there are the same number of items, THEN i need to order it by most recent date the product was ordered.

    Any help greatly appreciated. I am using SQL Server 2005

    Cheers

    DW

    Darryl Wilson
    darrylw99@hotmail.com

  • July 21, 2009 at 3:19 am

    #1027474

    To get the top 6 other products on the same order as a given product:

    SELECTtop 6

    ,ProductOrderOther.ProductId

    ,COUNT(*)as ProductOrderOtherCnt

    FROMOrderItems

    JOINOrderItemsAS ProductOrderOther

    on ProductOrderOther.OrderId = OrderItems.OrderId

    and ProductOrderOther.ProductId OrderItems.ProductId

    WHEREOrderItems.ProductId = 123

    GROUP BY ProductOrderOther.ProductId

    ORDER BY ProductOrderOtherCnt

    SQL = Scarcely Qualifies as a Language

  • July 21, 2009 at 9:38 am

    #1027715

    Have a look at dense_rank. I think that is what you are looking for.

    Gives you a way of assigning a value of importance to data. Have used it beofre for weeks in a month so that I could pull out data for only the first week. Best six selling items ordered by sales value etc.

    Ells.

    😎

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply