DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5
  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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.