Query with random result

Question

I have the query below, used in an internal routine, for distribution of items for separation in data collectors:

Select distinct IP, USUARIO, 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 3

In the above situation, it is always ordered by the login date/time on the system. With this the collector will always receive different items for separation from the previous day. But what happens is that many times the user enters the system at the turn entry and does not come out more until the end, ai at the time of distribution he always ends up receiving items from the same family, which for them is bad.

In this case, there is some command of the type SORT where each execution of the select above it randomly sorts?

UPDATE: I’m using Oracle SQL

Answer 1

SQL Server

If it is SQL Server, then you can use NewId() in the Order By.

SELECT
    DISTINCT IP,
    USUARIO,
    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
    NEWID()

Oracle

If it is Oracle, DBMS_RANDOM.RANDOM()

SELECT
   *
FROM
(
    SELECT DISTINCT
        IP,
        USUARIO,
        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.RANDOM()
)

Postgresql

If it is Postgresql, RANDOM().

SELECT
    DISTINCT IP,
    USUARIO,
    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
    RANDOM()

Mysql

If it is Mysql, RAND().

SELECT
    DISTINCT IP,
    USUARIO,
    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
    RAND()