Ensure query result random sorting

Asked

Viewed 235 times

0

I have the query below, which I use to check IP’s valid collectors in the network for distribution of items for separation:

Select distinct IP, USUARIO, trunc(DATA)
from
PCN_ROMANEIO_ACESSO    PRA
Where pra.STATUS = 'OK'
AND trunc(pra.DATA) = trunc(sysdate)
AND PRA.DATA_SAIDA IS NULL
AND IP <> '192.168.204.1'
AND NIVEL = 'S'
order by dbms_random.value

With the dbms_random.value it eventually changes the position of the query results, but most of the time it maintains the same positions as the previous execution, as an example:

inserir a descrição da imagem aqui

Is there any way, only with Oracle SQL, to "ensure" that the positions are all changed with each query execution?

sql oracle

  • I don’t know much about Oracle. But isn’t that possible : order by dbms_random.value,Rand() ? If I’m not mistaken this function internally uses the timestamp to sort randomly, so would guarantee the different result.

  • @Rafaelsalomão exists dbms_random.Andom, I put both but still gets the same result in up to 3 plays....

2 answers

1

You only have 3 columns to sort. Try to force the Random limits to generate a number between 1 and 3

dbms_random.value(1,3)

Of course being only 3 hypotheses, it is quite likely to come out some executions with repeated order.

1

Has how many occurrences in the table ?

I have done a similar report and this problem did not occur but there are thousands of data in the table, with few cases the chance to draw them is high.

In any case try so :

select IP, USUARIO, data
from  (
Select distinct IP, USUARIO, trunc(DATA) data
from
PCN_ROMANEIO_ACESSO    PRA
Where pra.STATUS = 'OK'
AND trunc(pra.DATA) = trunc(sysdate)
AND PRA.DATA_SAIDA IS NULL
AND IP <> '192.168.204.1'
AND NIVEL = 'S'
)
order by dbms_random.value

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.