Home » SQL & PL/SQL » SQL & PL/SQL » Query to display prime numbers
Query to display prime numbers [message #144162] Tue, 25 October 2005 06:16 Go to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
Can anybody help me in writing a query which displays first 100 prime numbers
Thanks,
Sreedevi
Re: Query to display prime numbers [message #144179 is a reply to message #144162] Tue, 25 October 2005 06:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Create a function like:
create or replace function is_prime( p_n in number ) return 
NUMBER
as
 l_stop number := ceil(sqrt(p_n));
begin
 for i in 2 .. l_stop
 loop
 if ( mod(p_n,i) = 0 )
 then
  return 0;
 end if;
 end loop;

 return 1;
end;


Afterwards, you can use it in a select like:
select level
  from dual
 where is_prime(level) = 1
 connect by level < 101
This works in 10g, what version are you on?

MHE
Re: Query to display prime numbers [message #144185 is a reply to message #144179] Tue, 25 October 2005 07:26 Go to previous messageGo to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
Thank you very much. Here we are creating a function and then using it. Cant we obtain the same result by a single query?
Thanks
Sreedevi
Re: Query to display prime numbers [message #144188 is a reply to message #144185] Tue, 25 October 2005 07:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
sreedevi_83 wrote on Tue, 25 October 2005 14:26

Cant we obtain the same result by a single query?
Since Oracle hasn't got a built-in function for this: no. So you create your own function. You might want to make it DETERMINISTIC though.

MHE
Re: Query to display prime numbers [message #144192 is a reply to message #144188] Tue, 25 October 2005 07:46 Go to previous messageGo to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
okay. Thank you
Re: Query to display prime numbers [message #144194 is a reply to message #144162] Tue, 25 October 2005 07:49 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Sreedevi,
Here is the code for Generate first 100 prime numbers

declare
ln_Counter NUMBER;
ln_primeNo NUMBER;
i NUMBER;
flag NUMBER;
begin
dbms_output.put_line(1);
ln_primeNo := 2;
ln_Counter := 1;

loop
FOR i IN 2..(ln_primeNo-1) LOOP
if MOD(ln_primeNo,i) = 0 then
flag := 1;
exit;
end if;
if MOD(ln_primeNo,i) != 0 then
flag := 0;
end if;
END LOOP;
if flag = 0 then
ln_Counter := ln_Counter + 1;
dbms_output.put_line(ln_primeNo);
end if;
ln_primeNo := ln_primeNo + 1;
exit when ln_Counter >= 100;
end loop;
dbms_output.put_line(ln_Counter);
end;

Regards
Sanka Paul
Re: Query to display prime numbers [message #144197 is a reply to message #144194] Tue, 25 October 2005 08:00 Go to previous messageGo to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
Hi Sanka,
Thank you very much. I wanted to get list of prime numbers through sql query and not thru procedure. Any how thanx for responding. Maaher's function seems to be more optimised than yours. I suggest you to see it.
Thanks,
Sreedevi
Re: Query to display prime numbers [message #146990 is a reply to message #144162] Tue, 15 November 2005 02:39 Go to previous messageGo to next message
sharan_it
Messages: 140
Registered: July 2005
Location: Chennai
Senior Member
Hi Sree,

What is the need of getting prime numbers through query?

Regards,
Sharan
Re: Query to display prime numbers [message #147079 is a reply to message #144162] Tue, 15 November 2005 12:57 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
AskTom covered this recently.
Re: Query to display prime numbers [message #147145 is a reply to message #146990] Tue, 15 November 2005 22:49 Go to previous messageGo to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
Hi Sharan,
It was one of the interview question for me. So I asked for it. Thanks to Metzer for giving me more information that has been covered by Tom.
Regards,
Sreedevi.


sharan_it wrote on Tue, 15 November 2005 14:09

Hi Sree,

What is the need of getting prime numbers through query?

Regards,
Sharan

Re: Query to display prime numbers [message #147179 is a reply to message #147079] Wed, 16 November 2005 02:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Art Metzer wrote on Tue, 15 November 2005 19:57

AskTom covered this recently.

I knew I got it from somewhere, but couldn't remember from where exactly. It just sat around in one of my boxes. I give all credit to Tom Wink

MHE
Re: Query to display prime numbers [message #147269 is a reply to message #144197] Wed, 16 November 2005 22:33 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
sreedevi_83 wrote on Tue, 25 October 2005 23:00

Hi I wanted to get list of prime numbers through sql query and not thru procedure.


Prime numbers up to n - no PL/SQL:

select a.prime
from (
        select level AS prime
        from dual
        connect by level < &n
) a
where not exists (
        select 1
        from dual b
        where mod(a.prime, level) = 0
        and level > 1
        connect by level <= a.prime / 2
)
/


_____________
Ross Leishman
Re: Query to display prime numbers [message #147297 is a reply to message #147269] Thu, 17 November 2005 01:53 Go to previous messageGo to next message
sudhir.sukumar
Messages: 52
Registered: August 2005
Location: India
Member

Very Impressive Ross.

Re: Query to display prime numbers [message #147311 is a reply to message #147297] Thu, 17 November 2005 02:59 Go to previous messageGo to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
Hi Ross,
Excellent Answer.Thanq Very much.
Regards,
Sree
Re: Query to display prime numbers [message #149708 is a reply to message #147311] Sat, 03 December 2005 01:33 Go to previous messageGo to next message
chinni_00
Messages: 30
Registered: October 2005
Member
hi Ross,

when i was go throughing the previous topics in the forum i came across this topic
and when i run the query which you gave is showing some errors
select a.prime
from (
        select level AS prime
        from dual
        connect by level < &n
) a
where not exists (
        select 1
        from dual b
        where mod(a.prime, level) = 0
        and level > 1
        connect by level <= a.prime / 2
)


The errors are

Enter value for n: 50
old 5: connect by level < &n
new 5: connect by level < 50
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

Please correct me if i went wrong in understanding the query.

Thanks
Regards
Re: Query to display prime numbers [message #149709 is a reply to message #149708] Sat, 03 December 2005 02:58 Go to previous messageGo to next message
bsubbu
Messages: 42
Registered: November 2005
Location: Hyderabad
Member
hi chinni..
no it is working chinni...no errors

see my outuput


SQL> r
1 select a.prime
2 from (
3 select level AS prime
4 from dual
5 connect by level < &n
6 ) a
7 where not exists (
8 select 1
9 from dual b
10 where mod(a.prime, level) = 0
11 and level > 1
12 connect by level <= a.prime / 2
13* )
Enter value for n: 10
old 5: connect by level < &n
new 5: connect by level < 10

PRIME
----------
1
2
3
5
7
Regards
Subbu
Re: Query to display prime numbers [message #149716 is a reply to message #149709] Sat, 03 December 2005 04:41 Go to previous messageGo to next message
chinni_00
Messages: 30
Registered: October 2005
Member
hi Subbu,

There is no change in the code which i wrote and which you gave me even though i tried your code even then iam getting that error what could be wrong

can anyone explain regarding this

Thanks In Advance
Regards
chinni
Re: Query to display prime numbers [message #149717 is a reply to message #149716] Sat, 03 December 2005 04:44 Go to previous messageGo to next message
chinni_00
Messages: 30
Registered: October 2005
Member
sorry for replying again the error msg is


Quote:

Enter value for n: 10
old 5: connect by level < &n
new 5: connect by level < 10
ERROR:
ORA-01436: CONNECT BY loop in user data


can anyone explain regarding this error

Thanks In Advance
Regards
chinni
Re: Query to display prime numbers [message #149719 is a reply to message #149717] Sat, 03 December 2005 05:15 Go to previous messageGo to next message
sudhir.sukumar
Messages: 52
Registered: August 2005
Location: India
Member

Tell us the database version you are trying this on?
Re: Query to display prime numbers [message #149721 is a reply to message #149719] Sat, 03 December 2005 05:21 Go to previous messageGo to next message
chinni_00
Messages: 30
Registered: October 2005
Member
hi sudhir,

Quote:

Oracle8i Enterprise Edition Release 8.1.7.


This is the version iam trying this query

Regards
Re: Query to display prime numbers [message #149723 is a reply to message #149721] Sat, 03 December 2005 05:38 Go to previous messageGo to next message
sudhir.sukumar
Messages: 52
Registered: August 2005
Location: India
Member

Does not work on 8.1.7

SQL*Plus: Release 8.1.7.0.0 - Production on Sat Dec 3 14:42:36 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production


sql> select a.prime
  2  from (
  3          select level AS prime
  4          from dual
  5          connect by level < &n
  6  ) a
  7  where not exists (
  8          select 1
  9          from dual b
 10          where mod(a.prime, level) = 0
 11          and level > 1
 12          connect by level <= a.prime / 2
 13  )
 14  /
Enter value for n: 15
old   5:         connect by level < &n
new   5:         connect by level < 15
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected
Re: Query to display prime numbers [message #149724 is a reply to message #149723] Sat, 03 December 2005 05:45 Go to previous messageGo to next message
chinni_00
Messages: 30
Registered: October 2005
Member
thanks sudhir for checking out for me

so in which version will this query wil work

Regards
chinni
Re: Query to display prime numbers [message #149725 is a reply to message #149717] Sat, 03 December 2005 05:47 Go to previous messageGo to next message
bsubbu
Messages: 42
Registered: November 2005
Location: Hyderabad
Member
chinni .
what is ur database verision..my database version is 9.2.0.1.0
for me its working....I am not sure that wether it will work with 8i or not...

ORA-01436 CONNECT BY loop in user data

Cause: The condition specified in a CONNECT BY clause caused a loop in the query, where the next record to be selected is a descendent of itself. When this happens, there can be no end to the query.

Action: Check the CONNECT BY clause and remove the circular reference.



check out this link saying that it will not work with 7,8,8i

http://laurentschneider.blogspot.com/2005/11/bible-of-oracle.html


Regards
Subbu

[Updated on: Sat, 03 December 2005 05:56]

Report message to a moderator

Re: Query to display prime numbers [message #149728 is a reply to message #144162] Sat, 03 December 2005 06:56 Go to previous messageGo to next message
chinni_00
Messages: 30
Registered: October 2005
Member
Thanks Subbu for your reply and the Link which you have specified i went through the link and got the point that it will not work in the 8i ok thanks

Regards
chinni
Re: Query to display prime numbers [message #149742 is a reply to message #149728] Sat, 03 December 2005 21:27 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

good job

[Updated on: Sat, 03 December 2005 23:16]

Report message to a moderator

icon9.gif  Re: Query to display prime numbers [message #327007 is a reply to message #144162] Fri, 13 June 2008 06:30 Go to previous messageGo to next message
gurupatham
Messages: 66
Registered: March 2008
Location: Chennai
Member
one more way to display the prime number upto 100.

select distinct Decode(Sum(decode(Mod(B.r , A.r) ,0 , 1, 0)) , 2 , B.r) Prime from (select rownum r from all_objects where rownum <= 100) A , (Select rownum r from all_objects where rownum <= 100) B group by B.r
Re: Query to display prime numbers [message #327023 is a reply to message #327007] Fri, 13 June 2008 07:56 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Pretty cool.
Re: Query to display prime numbers [message #327066 is a reply to message #327023] Fri, 13 June 2008 10:50 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
very quick should be:
with a as 
(SELECT rownum zeile FROM dual connect BY level <= 1000)
select n1 from (select a1.zeile n1, a2.zeile n2  from a a1, a a2)
where n2>0
and n1>n2
group by n1
having sum(decode(Mod(n1,n2),0,1,0))=1
order by n1
Re: Query to display prime numbers [message #327082 is a reply to message #327066] Fri, 13 June 2008 12:33 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Pretty cooler.
Previous Topic: Complex Query
Next Topic: Outer Join two derieved table with where clause
Goto Forum:
  


Current Time: Fri Dec 09 05:57:31 CST 2016

Total time taken to generate the page: 0.12611 seconds