pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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> 2012-03-07 20:38 ` Re: map row in one table with random row in another table Igor Neyman <ineyman@perceptron.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-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 ` Re: map row in one table with random row in another table 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-06 21:00 map row in one table with random row in another table rverghese <riyav@hotmail.com> 2012-03-07 20:38 ` Re: map row in one table with random row in another table Igor Neyman <ineyman@perceptron.com> @ 2012-03-07 20:48 ` rverghese <riyav@hotmail.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