Favorites Basket
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Favorites Basket

  1. #1
    Join Date
    Feb 2005

    Favorites Basket

    Hi Developers,
    I have created an Inventory system in the company I am working for, now the next they want me to make is to create something called the Favorite Basket, in which when I am entering a new Order say for example my order goes like this

    1- Desktop PC
    2- LCD Screen

    They want me to create an Analysis from previous orders containing my order items and display ( Employees who Ordered Items 1 & 2 ) have also ordered the following : say for example in this case

    1- Mouse :P
    2- Pen Drive

    Anyway my question is that how can I create this anaysis correctly is this an algorithim for it , please if anyone have done it before or have read about it. inform me.

    I wish we can charge them :P

    Aiman Al-Jumoay

  2. #2
    Join Date
    Sep 2002
    seems simple enough, just look at past orders for other people who have bought it and find the most bought item from those people

  3. #3
    Join Date
    Feb 2005
    yeah but how can i know what is the most item ordered with it.

    I mean the concept is clear but programmaticly what is the Algorithim for it ??
    should I start counting for each item ordered with this item ?

    if anyone have done it before or have read of how to do it, please help ot give links

    Aiman Al-Jumoay

  4. #4
    Join Date
    Aug 2002
    Colorado Springs
    Evidently there's two stages to this -- find the users who have previously ordered those same items, and find the items most commonly ordered by those users other than those items.

    I expect that inserting the ordered items into a temporary table would be helpful.

    You could find the users with code similar to ...
    Select username
    from   orders
    where  ordered_item in 
           select ordered_item
           from   temp_new_order
    group by username
    having count(distinct ordered_item) =
          (select count(*) from temp_new_order)
    ... and then find the most popular ordered items for those users with something like ...
    Select ordered_item
    Select ordered_item, sum(ordered_qty) sum_qty
    From   orders
    Where  username in (previous query inserted here)
    and    ordered_item not in 
           (select ordered_item from temp_new_order)
    group by ordered_item
    order by 2 desc
    Where rownum <= 5
    This may not be the best performing code, but at least it's a workable method to start with.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2005
    Toronto Canada
    I would be tempted to go with something along the lines of:

    SELECT ordered_item
    FROM (SELECT ordered_item, count(*)
          FROM orders
          WHERE order_no IN (SELECT order_no
                             FROM orders
                             WHERE ordered_item IN (list_from_current_order) and
                ordered_item NOT IN (list_from_current_order)
          GROUP BY ordered_item
          ORDER BY count(*) DESC)
    WHERE rownum < however_many_you_want_to_show
    to generate the list


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width