Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: difficult sql query, how to make a hitlist of couple ?

Re: difficult sql query, how to make a hitlist of couple ?

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sun, 05 Dec 2004 10:55:03 -0800
Message-ID: <1102272802.73@yasure>


georg.heiss wrote:

> Hi all,
>
> My Table "buyer_was_in_shop" has only 2 colums:
>
> buyer_id (pk), shop_concate (varchar2(400))
>
> select * from buyer_was_in_shop;
>
> BUYER_ID SHOP_CONCATE
> 875234 "24111:19112:23:1132:9"
> 100826 "21987:23"
> 123457 "9:19112"
> 783489 "43:987"
> 787638 "33:19112:9"
> .
>
> The Table has 12.000.000 Rows and the Maximum of shops per buyer is 7.
> How can i do a Ranking with a couple of shops with the maximum of same
> buyers?
> Example:
> Winner is the couple of shop 9 an 19112, because there is a maximum of
> 3 same buyers!
>
> Can i solve this complex Problem with PL/SQL ?
>
> Thanks Georg

First drop the column SHOP_CONCATE as it is the antithesis of relational design. Load the data into a relational table such as:

CREATE TABLE buyer_was_in_shop (

buyer_id  NUMBER(6),
shop_num  NUMBER(1),
shop_id   NUMBER(5));

Then the problem becomes solvable as something other than an exercise in agony.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Sun Dec 05 2004 - 12:55:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US