Skip site navigation (1) Skip section navigation (2)

map row in one table with random row in another table

From: rverghese <riyav(at)hotmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: map row in one table with random row in another table
Date: 2012-03-06 21:00:32
Message-ID: 1331067632664-5542231.post@n5.nabble.com (view raw or flat)
Thread:
Lists: 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-tp5542231p5542231.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Responses

pgsql-sql by date

Next:From: Adam CornettDate: 2012-03-07 18:28:28
Subject: Re-Sync Stand By Server
Previous:From: Julien RouhaudDate: 2012-03-06 10:44:38
Subject: Re: [ADMIN] pg_dump : no tables were found.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group