[Hint] Select of primes

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

Poston Mon, 31 Oct 2005 12:32 pm

Hello guys ... For those who do not know, I study Mathematics applied to Informatics at ULBRA. (IE, I love math!). It's been a while that I wanted to set up a select that generates the primes. Finally today I got!!! :-D

SQL of PRIMES
Code: Select all
select z.zz PRIMOS
from
(select rownum+1 zz
  from user_tables
  where rownum < 100
) z
where zz not in (
                select y.bb
                from (select rownum+1 aa from user_tables a where rownum < 100) x
                ,    (select rownum+1 bb from user_tables b where rownum < 100) y
                where bb<>aa
                  and mod(bb, aa)=0
                  and aa<=bb
                )
/
there he goes:
Code: Select all
SQL> select z.zz PRIMOS
  2  from
  3   (select rownum+1 zz
  4    from user_tables
  5    where rownum < 100
  6   ) z
  7  where zz not in (
  8                  select y.bb
  9                  from (select rownum+1 aa from user_tables a where rownum < 100) x
10                  ,    (select rownum+1 bb from user_tables b where rownum < 100) y
11                  where bb<>aa
12                    and mod(bb, aa)=0
13                    and aa<=bb
14                  )
15  /

   PRIMOS
---------
        2
        3
        5
        7
       11
       13
       17
       19
       23
       29
       31
       37
       41
       43
       47
       53
       59
       61
       67
       71
       73
       79
       83
       89
       97

25 rows selected.
SQL>
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, 01 Nov 2005 11:04 am

ai dr_gori, congratulations, every now and then you put some very interesting tips ... some are curiosities but others are of great value for those who work with oracle.

[] ´ s
anderson
Location: Toledo - PR

Anderson Nuernberg
---

Poston Tue, 01 Nov 2005 11:14 am

Thanks Anderson!

Really, I try to put things here that may benefit oracle partners and also because I don't want to forget these tips! :-) (I myself live by querying this forum and find tips that I even gave you forgot!) :-o

Thanks to all who collaborate also with the Forum, is to give a hint, or even say something!!!!

[] ´ s
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 Mon, 13 Oct 2008 6:21 pm

Today, I found other ways to return the primes in a SQL:
Code: Select all
SQL> select l prime_number
  2    from (select level l from dual connect by level <= 100)
  3       , (select level m from dual connect by level <= 100)
  4   where m<=l
  5   group by l
  6  having count(case l/m when trunc(l/m) then 'Y' end) = 2
  7   order by l
  8  /

                          PRIME_NUMBER
--------------------------------------
                                     2
                                     3
                                     5
                                     7
                                    11
                                    13
                                    17
                                    19
                                    23
                                    29
                                    31
                                    37
                                    41
                                    43
                                    47
                                    53
                                    59
                                    61
                                    67
                                    71
                                    73
                                    79
                                    83
                                    89
                                    97
otherwise faster than: Cartesian product (10 g only)
Code: Select all
SQL> with t as (select level l from dual connect by level <= 100)
  2  --
  3  SELECT l prim_num FROM
  4     (select * from t
  5       model
  6        dimension by (l dim)
  7        measures (l,2 temp)
  8         rules iterate (1000000) until (power(temp[1],2)>100)
  9           (l[DIM>TEMP[1]]=decode(mod(l[CV()],temp[1]),0,null,l[CV()]),
10            temp[1]=min(l)[dim>temp[1]])
11     )
12  WHERE l IS NOT NULL
13  /

  PRIM_NUM
----------
         1
         2
         3
         5
         7
        11
        13
        17
        19
        23
        29
        31
        37
        41
        43
        47
        53
        59
        61
        67
        71
        73
        79
        83
        89
        97

26 rows selected

SQL>
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 Mon, 29 Dec 2008 4:29 pm

Did a procedure with the tip, went like this:
Code: Select all
/* imaginando que a package PKG_UTIL exista e nela tenha sido declarado um REF CURSOR chamado C_CURSOR */

create or replace procedure NUM_PRIMOS(LIMITEMAX IN NUMBER
                                     , p_retorno OUT PKG_UTIL.C_CURSOR) is

                           
begin
   
   OPEN p_retorno for
   select P.primos
   from (select rownum+1 primos
             from user_tables
             where rownum < LIMITEMAX
             ) P
       where P.PRIMOs not in ( select y.bb
                               from ( select rownum+1 aa from user_tables a where rownum < LIMITEMAX) x
                               ,    (select rownum+1 bb from user_tables b where rownum < LIMITEMAX) y
                               where bb<>aa
                               and mod(bb, aa)=0
                               and aa<=bb
                             ) ;
 
end NUM_PRIMOS;

Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests