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

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)