SQL Puzzle | The Prime Number Puzzle – V

This is a variation in the Prime Puzzle . Here you have to return a list all prime numbers above a given number of N. And of course we cannot use any of the existing methods. 🙂

Please check out the sample input and the expected output below-

Other Prime Number related links-

Sample Input

DECLARE @PrimeNumbersBelow AS INT = 1000 ( Prime Numbers below this number )

Expected Output

Solution 1

--


;WITH CTE AS
(
	SELECT DISTINCT Number FROM Master..Spt_Values WHERE Number > 1 AND Number  <= 1000
)	
SELECT * FROM CTE
EXCEPT
(
	SELECT ( F1.number * F2.number ) a
	FROM CTE AS F1, CTE AS F2
	WHERE 
	F1.Number BETWEEN 2 AND CEILING (SQRT (1000))  
	AND F1.Number <= F2.Number
	AND (F1.Number * F2.Number) <= 1000
) 
ORDER BY number


--

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