Puzzle of the Week #12

With a single SELECT statement produce a list of first 10 prime numbers above a given number of N.

Expected Result: (for N=15)

```     Prime
----------
17
19
23
29
31
37
41
43
47
53

10 rows selected.```

Expected Result: (for N=50)

```     Prime
----------
53
59
61
67
71
73
79
83
89
97

10 rows selected.```

A correct answer (and workarounds!) will be published here in a week.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

8 thoughts on “Puzzle of the Week #12: Prime Numbers”

1. Sameer May 18, 2016 / 4:33 am

The Answer for Puzzle for week#12

select prime_number
from
(select l prime_number
from (select level l from dual
connect by level <= 1000) — Set the limit upto what you want you want
, (select level m from dual
connect by level <= 1000) — Set the limit upto what you want you want
where m<=l
group by l
having count(case l/m when trunc(l/m) then 'Y' end) = 2
order by l)
where rownum :p_num

• Zahar Hilkevich May 18, 2016 / 8:17 am

we don’t know what the upper limit will be (1000 or more), p_num – is it a parameter? If so, even p_num could be 1000.

` and `

around it to preserve all the characters from conversion to tags

2. Krishna Jamal May 20, 2016 / 9:32 pm

WITH x AS (SELECT &num Num FROM Dual),
y AS (SELECT Num+LEVEL-1 l1 FROM x CONNECT BY LEVEL <=100),
z AS (SELECT LEVEL l2 FROM x CONNECT BY LEVEL <= Num)
SELECT l1 FROM (
SELECT l1, ROW_NUMBER() OVER(ORDER BY l1) rn
FROM y, z
GROUP BY l1
HAVING COUNT(DECODE(l1/l2, TRUNC(l1/l2), 'Prime')) = 1
)
WHERE rn BETWEEN 1 AND 10;

• Zahar Hilkevich May 23, 2016 / 9:58 pm

It fails when n=1 or n=2 and even n=3 (it includes 25)

3. Pawan Kumar Khowal May 31, 2016 / 10:13 pm

Below is the generic & highly optimized solution – ( T-SQL , Version – 2012 ), Don’t know Oracle, Here I am using numbers table..

DECLARE @BeyondPrimeNumbers AS INT = 15
DECLARE @FetchPrimeNumber AS INT = 10

FROM
MASTER..SPT_VALUES num
WHERE
NOT EXISTS
(
SELECT 1 FROM MASTER..SPT_VALUES AS num1 WHERE num1.Number > 1
AND num1.Number < num.Number
AND num.Number % num1.Number = 0
)

• Pawan Kumar Khowal May 31, 2016 / 10:56 pm

DECLARE @BeyondPrimeNumbers AS INT = 1
DECLARE @FetchPrimeNumber AS INT = 1

FROM
MASTER..SPT_VALUES num
WHERE
NUMBER >= 2 AND