SQL Puzzle | 5 Prime Numbers above a given number

This is a variation in the Prime Puzzle . Here you have to return a list of first 5 prime numbers above a given number of N. Let’s say we want to fetch 5 prime numbers above number 15.

Please check out the sample input values and sample expected output below.

Sample Input

DECLARE @BeyondPrimeNumbers AS INT = 15 ( Prime Numbers above this number )

DECLARE @FetchPrimeNumber AS INT = 5 ( Total Prime Number Required )

Expected Output

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1 | Using Numbers table & Mod

--

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
)
 
 

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal Http://MSBISkills.com