Analytic Function: Even Distribution

From: Ed <ed_at_mail.com>
Date: Fri, 16 Feb 2007 16:03:47 -0500
Message-ID: <12tc71ljapm5i4b@corp.supernews.com>

Say I have a bunch of bowling players of different skill level as indicated by his avg_score in the table below.

I need to allot them into n teams (say 8), of equivalent strength on the TEAM level so no team ends up with mostly high-scorers and vic-versa.

(let's say players may not be evenly divided into teams because n numbers are "sick")

Is there a way to do to this ?

Thanks

10gR2> create table players (id integer primary key, avg_score number, team_no integer) ;

10gR2> desc players
Name Type
--------- -------
ID INTEGER
AVG_SCORE NUMBER
TEAM_NO INTEGER 10gR2> BEGIN
  2 FOR i IN 1..120
  3 LOOP
  4 INSERT INTO players (id, avg_score) VALUES(i,round(dbms_random.value(75,295)));   5 END LOOP;
  6 END ;
  7 /

10gR2> commit; Received on Fri Feb 16 2007 - 15:03:47 CST