Query with random result

Question

Query with random result

Navigation

#1 by (2 votes)

3

I have the following query, 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 sorted by the system login date / time. With this the collector will always receive different items for separation from the previous day. But what happens is that often the user enters the system at the entrance of the shift and does not go out until the end, then at the time of distribution he always ends up receiving items from the same family, which is bad for them.

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

UPDATE: I'm using Oracle SQL

sql oracle

asked by anonymous 24.10.2016 / 13:12

1 answer

Dynamic forms on the same page in Jquery and PHP Automate enter input on command line while executing a bash script

score 2 · Accepted Answer

SQL Server

If it is SQL Server, then you can use NewId( ) in 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's 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's 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()