From: rverghese <riyav@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: map row in one table with random row in another table
Date: Tue, 6 Mar 2012 13:00:32 -0800 (PST)
Message-ID: <1331067632664-5542231.post@n5.nabble.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: riyav@hotmail.com
Subject: Re: map row in one table with random row in another table
In-Reply-To: <1331067632664-5542231.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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