SQL Puzzle | The Prime Numbers Puzzle

Puzzle Statement

I got this puzzle on facebook. The puzzle says you have to find prime numbers up to a certain limit for example let’s say 50. This question now a days is one of the favorite question of interviewers from Tier 1 organizations.

Sample Input

Find prime numbers up to 50

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 #

--
--Here I am using Master..Spt_Values table to get numbers, You can use your own table.


SELECT DISTINCT Number
FROM 
	MASTER..SPT_VALUES num
WHERE 
	Number >= 2 AND Number <= 50 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 Kumar Khowal 

Http://MSBISkills.com