Retrieve first 100 prime numbers using SQL Query
May 11, 2017 1 Comment
On an interview , one of our colleague asked participant the following question:
“How can you generate prime numbers using just query?”
Answering to this question using PL/SQL is simple using loop. But using SELECT 🙂 not so simple 🙂
I found the answer and want to share with you.
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
About Mariami Kupatadze
DBA Knowledge Base 
Its been long had tried similar SQL requirement thought let give my attempt.
It used New LATERAL View Hint.!
SELECT 1 FROM DUAL
UNION ALL
SELECT COL1
FROM
(SELECT COL1 ,
COUNT(
CASE
WHEN MOD(COL1 ,
CASE
WHEN COL2 = 1
OR COL2 = COL1
THEN NULL
ELSE COL2
END) = 0
THEN 1
ELSE NULL
END) CNT
FROM
(SELECT LEVEL + 1 COL1 FROM DUAL CONNECT BY LEVEL <= 99
) L ,
LATERAL — Lateral hint to add join condition in From clause it self.
(SELECT LEVEL COL2 FROM DUAL CONNECT BY LEVEL <= L.COL1
)
GROUP BY COL1
ORDER BY 1
)
WHERE CNT = 0;