Query with Random Search is possible??

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 06 Mar 2006 8:57 pm

We need to do a random query, to bring existing records in the table but in random, there's someone there who'vê done that.If someone knew and could help me I'd really appreciate it.

Thanks a lot ...

JCTQ: idea:
jctq
Location: São Gonçalo

Att,
Julio Quintão

Poston Mon, 06 Mar 2006 11:55 pm

dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 07 Mar 2006 10:16 am

Thanks Gori!!!

But what I really wanted is to do a query to a table that have 1000 records and select only 150 aleátoriamente, how to do this??

Thank you very much
jctq
Location: São Gonçalo

Att,
Julio Quintão

Poston Tue, 07 Mar 2006 10:22 am

Yes, put it in your searches for a
Code: Select all
WHERE ROWNUM<=150
Example:
Code: Select all
SQL> SELECT *
  2  FROM
  3    (SELECT *
  4     FROM EMP
  5     ORDER BY DBMS_UTILITY.GET_HASH_VALUE(TO_CHAR(dbms_utility.get_time)||ename,2,1048576)
  6    )
  7  WHERE ROWNUM <=3
  8  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7839 KING       PRESIDENT            17-NOV-81       5000                    10

SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

SQL>
. : shock:
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 07 Mar 2006 10:39 am

Ai Gori, thanks man. ...
This portal is very good ....

Hugs ...: lol:
jctq
Location: São Gonçalo

Att,
Julio Quintão

Poston Wed, 16 Sep 2009 9:20 pm

I need to generate randomly from a customer table that has 1 million records and want to only generate the number of 50,000 customers, how can I do that? Have questions on how to use DBMS_UTILITY.GET_HASH_VALUE to make this query..Can someone help me?
mariogus
Location: Porto Alegre - RS

Poston Thu, 17 Sep 2009 3:36 pm

Thomas has posted an example of a studied him and you get the easy solution!
RodrigoValentim
Location: Salvador - BA

Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Poston Fri, 18 Sep 2009 12:12 pm

Staff-the following SQL: But what happens is that it gets lost in the where clause and brings the other data ... also ... What is being done wrong?

(dbms_utility. get_time) ||.3 dimcli_id .429604886)-have questions about these last grifados parameters in bold.

SQL:
Code: Select all
SELECT *
FROM
(
SELECT *
FROM dim_cliente_suat d
where d.tipo_conta = 'D'
and d.perfil_cobranca_conta = 'PAG'
and d.brand = 'terra'
and d.dt_cadastro_conta < sysdate - 90
and (d.status_conta = 'CA' and d.dt_status_conta between sysdate -240 and sysdate -90)
or (d.status_conta = 'AT')
ORDER BY DBMS_UTILITY.GET_HASH_VALUE(TO_CHAR(dbms_utility.get_time)||dimcli_id,3,429604886)
)
WHERE ROWNUM <=50000
mariogus
Location: Porto Alegre - RS

Poston Fri, 18 Sep 2009 1:56 pm

GET_HASH_VALUE Function This function computes the hash value for the given string.
Syntax
Code: Select all
DBMS_UTILITY.GET_HASH_VALUE (
   name      VARCHAR2,
   base      NUMBER,
   hash_size NUMBER)
  RETURN NUMBER;
Parameters Table 104-20 GET_HASH_VALUE Function Parameters Parameter Description name-String to be hashed.
base-the Base value for the returned hash value to start at.
hash_size-Desired size of the hash table.
victorhugomuniz
Location: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Image JavaBlackBelt

Poston Wed, 07 Oct 2009 11:31 am

I used the Dbms_Randon. Value in Order By
Code: Select all
Select * From (
Select
   *
From   
emp
Order By Dbms_Random.Value) a
Where
Rownum < 3
Estevan.
esttevan
Location: Nova Hartz - RS


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: No registered users and 5 guests