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

Number
17
19
23
29
31

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