Teja Teja - 1 year ago 41
SQL Question

Get Prime Number records in Oracle using ROWNUM

I am trying to get all the prime(row) numbered records from my table.Can someone please shed some light on how to solve this problem?

Here is my sample data as below.

EMPID EMPNAME
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J


Required output:

EMPID EMPNAME
2 B
3 C
5 E
7 G


If I have huge data how do I get the output like this instead of using IN operator?

Answer Source

Sieve of Eratosthenes

CREATE TABLE primes (
    num number PRIMARY KEY
);

INSERT INTO primes (num)
SELECT LEVEL + 1
FROM dual
CONNECT BY LEVEL < 1000;

DELETE FROM primes p1
WHERE EXISTS (
    SELECT NULL
    FROM primes p2
    WHERE p2.num < p1.num
    AND MOD(p1.num, p2.num) = 0
);

And then

SELECT emps.*
FROM emps
INNER JOIN primes ON primes.num = emps.EMPID;

Or

SELECT EMPID, EMPNAME
FROM (
    SELECT ROWNUM AS rn, emps.EMPID, emps.EMPNAME
    FROM emps
)
INNER JOIN primes ON primes.num = rn;

If you don't want to calculate the primes, you could add them from existing data: List of small primes

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download