Teja - 1 year ago 46

SQL Question

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?

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

```
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**