Atul Takiar

• Nov 9th, 2005

 

select * from table_name where (rowid,1) in ( select rowid,mod(rownum,2) from table_name )

tez

• Nov 30th, 2005

 

i thnk that it fetches odd numbered rows but not prime numbered

Eugene

• Feb 17th, 2006

 

No formula has ever been found that accounts for all of the known prime numbers, prime numbers cannot be calculated. Instead, each number must be tested individually to see if it has any factors. Only when it is demonstrated that a number has no factors besides itself and 1 has the number been "proved" to be prime.

ashok

• Feb 23rd, 2006

 

how to get the prime number rows from table ie lik...

santosh

• Feb 27th, 2006

 

this query returns odd valuesselect * from (select rownum r,emp_num from table_name )where mod(r,2)!=0

Tandra

• Mar 6th, 2006

 

select empno, ename, rn
  from (select e.*, row_number() over (order by empno asc) rn
         from emp )
where mod(rn, 2)=1;

ash

• Apr 12th, 2006

 

I tried in sql but sql is not sufficient.  U may create a function like this

 create or replace function fn_chk_pm(v_num in number) return number
 is
 v_flag number:=0;
 v_j number:=round(v_num/2);
 begin
     for v_cnt in 2..v_j
     loop
           if mod(v_num,v_cnt)=0 then
                  v_flag:=1;
                 exit when v_flag=1;
          end if;
     end loop;
 if v_flag=0 then
        return 1;
 end if;
 if v_flag=1 then
       return 0;
 end if;
 end;

Assumuming the table as follows

SQL> select * from dummyag;

      COL1 COL2
---------- -
         2 b
         3 c
         4 d
         1 a

I can call it from sql statement as follows:

SQL>

 1  select a.rn,a.col1,a.col2 from (select rownum rn,col1,col2 from dummyag)  a
  2  where fn_chk_pm(a.rn)=1
  3* and a.rn!=1

With the result,

        RN       COL1 C
---------- ---------- -
         2          3 c
         3          4 d

Srikanth Vunyala

• Sep 17th, 2007

 

Dear Friends,

Please try this and confirm,

((select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp))
Minus
(select * from emp where (rowid,0) in (select rowid,mod(rownum,3) from emp)))
union
(select * from emp where (rowid,3) in (select rowid,rownum from emp));

Thanks & Regards,
99630 86664.

rohan1404

 Profile Answers by rohan1404 

• Feb 2nd, 2010

 

SELECT l prime_number 
FROM (SELECT level l FROM dual CONNECT BY level <= 100), 
(select level m from dual CONNECT BY level <= 100)
WHERE m<=l 
GROUP BY l 
HAVING COUNT(case l/m when trunc(l/m) then 'Y' end) = 2
ORDER BY l

Babu

• Nov 3rd, 2011

 

select * from (select rownum num,e.* from emp e) ab where mod(ab.num,2)=1 and mod(ab.num,3)!=0

kirathakudu

 Profile Answers by kirathakudu 

• Nov 27th, 2011

 

Code
SELECT column1,column2,rownum FROM TABLE_NAME GROUP BY column1,column2,rownum HAVING mod(rownum,2)!=0 AND mod(rownum,3)!=0 ORDER BY rownum.

See below example on emp table.

Code
SELECT empno,ename,rownum FROM emp GROUP BY empno,ename,rownum HAVING mod(rownum,2)!=0 AND mod(rownum,3)!=0 ORDER BY rownum;

Note Order by is optional...! if you want select more colums you can add them in both select & Group by class ------

by..kiru...

KARRTHIK

• Dec 3rd, 2011

 

Code
    n NUMBER;


    i NUMBER;


    counter NUMBER;


BEGIN


    n:=&n;


    i:=1;


    counter:=0;


    IF n=1


        THEN DBMS_OUTPUT.put_line('1 is a prime No.');


    ELSE IF n=2


        THEN DBMS_OUTPUT.put_line('2 is even prime');


    ELSE


        FOR i IN 1..n LOOP


            IF MOD(n,i)=0


                THEN counter:=counter+1;


            END IF;


        END LOOP;


    END IF;


END IF;


    IF counter=2


        THEN DBMS_OUTPUT.put_line(n||' is a prime No.');


    ELSE


        DBMS_OUTPUT.put_line(n||' is a not prime No.');


    END IF;


END;

It is successfully exceuted

VIJAY SHARMA

• Sep 24th, 2012

 

Code
 


SELECT SEQNO "PRIME NUMBERS"


FROM(


     SELECT SEQNO


     FROM( 


          SELECT ROWNUM SEQNO


          FROM ALL_OBJECTS


          GROUP BY ROWNUM


          HAVING ROWNUM <= &GIVENNUM


         ),


         (


          SELECT ROWNUM SEQNO2


          FROM ALL_OBJECTS


          WHERE ROWNUM <= &GIVENNUM


         ) A2


    GROUP BY CUBE(SEQNO,SEQNO2)


    HAVING GROUPING_ID(SEQNO,SEQNO2) = 0 AND SEQNO >= SEQNO2 AND


    MOD(SEQNO,SEQNO2) = 0


    )


GROUP BY SEQNO


HAVING COUNT(*) IN(1,2)


ORDER BY SEQNO;