Retrieve first 100 prime numbers using SQL Query

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

Advertisements

One Response to Retrieve first 100 prime numbers using SQL Query

  1. Deepak Mahto says:

    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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: