[ADMIN] about select use random

[wangqi]

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!

[Tom Lane]

¡ö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

[wangqi]

Thanks Tom, I try it.

Post by Tom Lane

Post by wangqi
■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:[phone]
Fax:[phone]
http://www.edgesoft.cn
mailto:***@edgesoft.cn
----------------------------------------------------

[wangqi]

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?

Post by Tom Lane

Post by wangqi
■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:[phone]
Fax:[phone]
http://www.edgesoft.cn
mailto:***@edgesoft.cn
----------------------------------------------------