Thread: about select use random
Hi everyone ■SQL-1 CREATE TABLE wk_mclid1( id1 integer , PRIMARY KEY(id1) ); ■SQL-2 INSERT INTO wk_mclid1(id1) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10) GO ■SQL-3 select * from wk_mclid1 where id1= (round((random() * (10-1))::integer) + 1)::integer; When we excute SQL-3,sometimes the result was 0 record or more than 1 record. Why? ============================================================================================ select * from wk_mclid1 where id1= (round((random() * (10-1))::integer) + 1)::integer; id1 ------ 9 ============================================================================================ select * from wk_mclid1 where id1= (round((random() * (10-1))::integer) + 1)::integer; id1 ------ 4 6 7 ============================================================================================ select * from wk_mclid1 where id1= (round((random() * (10-1))::integer) + 1)::integer; id1 ------ 0 record(s) selected [Fetch MetaData: 94/ms] [Fetch Data: 0/ms] ============================================================================================ thanks!
wangqi <wangqi@edgesoft.cn> writes: > ��SQL-3 > select * from wk_mclid1 where id1= (round((random() * (10-1))::integer) > + 1)::integer; > When we excute SQL-3,sometimes the result was 0 record or more than 1 > record. > Why? random() is re-evaluated for each row scanned by the SELECT. If you don't want that, you can use a WITH to lock down the result of a single random() call. regards, tom lane
Thanks Tom, I try it. 于 2012-7-25 10:55, Tom Lane 写道: > wangqi <wangqi@edgesoft.cn> writes: >> ■SQL-3 >> select * from wk_mclid1 where id1= (round((random() * (10-1))::integer) >> + 1)::integer; >> When we excute SQL-3,sometimes the result was 0 record or more than 1 >> record. >> Why? > random() is re-evaluated for each row scanned by the SELECT. > > If you don't want that, you can use a WITH to lock down the result of a > single random() call. > > regards, tom lane > -- ---------------------------------------------------- Yours sincerely, Qi Wang Edgesoft (China) Co.,Ltd F26th Block B,Zhongli Building, No.32 Huoju Road, Hi-Tech Zone,Dalian,China Zip:116023 Tel:+86-411-84753511 Fax:+86-411-84753577 http://www.edgesoft.cn mailto:wangqi@edgesoft.cn ----------------------------------------------------
Hi Tom If you don't want that, you can use a WITH to lock down the result of a single random() call. How should I do it? 于 2012-7-25 10:55, Tom Lane 写道: > wangqi <wangqi@edgesoft.cn> writes: >> ■SQL-3 >> select * from wk_mclid1 where id1= (round((random() * (10-1))::integer) >> + 1)::integer; >> When we excute SQL-3,sometimes the result was 0 record or more than 1 >> record. >> Why? > random() is re-evaluated for each row scanned by the SELECT. > > If you don't want that, you can use a WITH to lock down the result of a > single random() call. > > regards, tom lane > -- ---------------------------------------------------- Yours sincerely, Qi Wang Edgesoft (China) Co.,Ltd F26th Block B,Zhongli Building, No.32 Huoju Road, Hi-Tech Zone,Dalian,China Zip:116023 Tel:+86-411-84753511 Fax:+86-411-84753577 http://www.edgesoft.cn mailto:wangqi@edgesoft.cn ----------------------------------------------------