Ensure random sorting of query result

Question

Ensure random sorting of query result

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I have the following query, which I use to verify valid IP's of 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 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:

Isthereanyway,withOracleSQLonly,to"ensure" that the positions are all changed at every query execution?

sql oracle

asked by anonymous 21.07.2017 / 14:22

2 answers

Internal elements overlapping the section I can not add items in scroolview

score 1 · Answer 1

How many occurrences in the table?

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

In any case try this:

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

score 1 · Answer 2

You only have 3 columns to sort. Try to force 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 that some executions will come out with repeated ordering.