map row in one table with random row in another table

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

map row in one table with random row in another table
3+ messages / 2 participants
[nested] [flat]

* map row in one table with random row in another table
@ 2012-03-06 21:00  rverghese <riyav@hotmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: rverghese @ 2012-03-06 21:00 UTC (permalink / raw)
  To: pgsql-sql

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'

I also tried
select generate_series(1,10), (select  segment from segments  order by
random() limit 1 )



--
View this message in context: http://postgresql.1045698.n5.nabble.com/map-row-in-one-table-with-random-row-in-another-table-tp5542...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: map row in one table with random row in another table
@ 2012-03-07 20:38  Igor Neyman <ineyman@perceptron.com>
  parent: rverghese <riyav@hotmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Igor Neyman @ 2012-03-07 20:38 UTC (permalink / raw)
  To: rverghese <riyav@hotmail.com>; pgsql-sql

> -----Original Message-----
> From: rverghese [mailto:riyav@hotmail.com]
> Sent: Tuesday, March 06, 2012 4:01 PM
> To: pgsql-sql@postgresql.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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: map row in one table with random row in another table
@ 2012-03-07 20:48  rverghese <riyav@hotmail.com>
  parent: Igor Neyman <ineyman@perceptron.com>
  0 siblings, 0 replies; 3+ messages in thread

From: rverghese @ 2012-03-07 20:48 UTC (permalink / raw)
  To: pgsql-sql

That's cool, thanks! 
Yeah I just want to create some test data, so it's not something I would run
often.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/map-row-in-one-table-with-random-row-in-another-table-tp5542...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2012-03-07 20:48 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-06 21:00 map row in one table with random row in another table rverghese <riyav@hotmail.com>
2012-03-07 20:38 ` Igor Neyman <ineyman@perceptron.com>
2012-03-07 20:48   ` rverghese <riyav@hotmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox