I have a problem with using random numbers together with temp tables. I have a table from which I want to select some random user_id
's and then in the second table select some more, but with no repetition from the first one. A quick example follows (it is simplified, so pls don't write to simply select user_id
s in the first place):
drop table if exists test;
create table test (
user_id int,
g int);
insert into test values (1, 1);
insert into test values (2, 1);
insert into test values (3, 1);
insert into test values (4, 1);
insert into test values (5, 1);
insert into test values (6, 1);
with temp as (
select t.user_id
from (select tt.*, row_number() over (partition by tt.g order by randomint(100)) as seqnum
from test tt) t
where t.seqnum <= 2
)
select user_id from temp union all
select user_id from test where user_id not in (select user_id from temp)
The result of the query shoul ideally return all user_id
s, however, it results in an incomplete set but with repetitions. Do you know how I can solve this problem?
Aucun commentaire:
Enregistrer un commentaire