Puzzle of the Week #12: Prime Numbers

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.

To submit your answer (one or more!) please start following this blog and add a comment to this post.

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

My Oracle Group on Facebook:

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

Would you like to read about many more tricks and puzzles?

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

 

Advertisements

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.
      Please correct your query: please put

       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

    SELECT DISTINCT TOP (@FetchPrimeNumber) Number
    FROM
    MASTER..SPT_VALUES num
    WHERE
    Number >= @BeyondPrimeNumbers AND
    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

      Added one more condition..

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

      SELECT DISTINCT TOP (@FetchPrimeNumber) Number
      FROM
      MASTER..SPT_VALUES num
      WHERE
      NUMBER >= 2 AND
      Number >= @BeyondPrimeNumbers AND
      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
      )

      • Zahar Hilkevich June 6, 2016 / 8:30 pm

        The puzzle was to do this in a single SELECT statement – this is good but not exactly what was requested.

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