Re: map row in one table with random row in another table

> -----Original Message-----
> From: rverghese [mailto:riyav(at)hotmail(dot)com]
> Sent: Tuesday, March 06, 2012 4:01 PM
> To: pgsql-sql(at)postgresql(dot)org
> Subject: map row in one table with random row in another table
>
> Hi, I am trying to map every row in one table with a random row in
> another.
> So for e.g. , for each network in 1 table I am trying to map random
> segments from the other table. I have this sql below, but it always
> applies the same random segment that it picks to all the rows for the
> network. I want each row to have a random segment value. I'm just
using
> the generate_series function to generate id's as an e.g.
> Any suggestions?
>
> My Query
> select id, seg_list from (select generate_series(1,10) as id) as X,
> (select segment from segments order by random() limit 1 ) as Y
>
> I get
>
> 1;'cob0002'
> 2;'cob0002'
> 3;'cob0002'
> 4;'cob0002'
> 5;'cob0002'
> 6;'cob0002'
> 7;'cob0002'
> 8;'cob0002'
> 9;'cob0002'
> 10;'cob0002'
>
> What I want is
>
> 1;'cob0002'
> 2;'cob0008'
> 3;'cob0006'
> 4;'cob0004'
> 5;'cob0002'
> 6;'cob0007'
> 7;'cob0003'
> 8;'cob0004'
> 9;'cob0009'
> 10;'cob0001'
>

Try this:

Select distinct on (id) id, segment
>From (select generate_series(1,10) as id) as X,
(select segment from segments) as Y
Order by id, random();

Depending on the size of your tables, performance could become an issue.

Regards,
Igor Neyman