SQL Puzzle | Biggest Prime Number Divisor of an Input Number

This is a variation in the Prime Puzzle . Here you have to find the biggest prime number divisor of an input number.

Divisor – The number you divide by. E.g.

dividend ÷ divisor = quotient

Example: in 12 ÷ 3 = 4, 3 is the biggest prime divisor for number 12.

For details please refer – https://www.mathsisfun.com/definitions/divisor.html

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

Sample Input

DECLARE @ INT = 39
( Biggest Prime Number Divisor for this input number )

Expected Output

 Number 13

Solution 1

 ``` -- ;WITH CTE AS ( SELECT DISTINCT Number FROM Master..Spt_Values WHERE Number > 1 AND Number <= 1000 ) SELECT MAX(Number) Number FROM ( 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 ) )r WHERE @ % Number = 0 -- ```

Solution 2

 ``` -- DECLARE @ INT = 39 ;WITH CTE AS ( SELECT DISTINCT Number FROM Master..Spt_Values WHERE Number > 1 AND Number <= 1000 ) SELECT MAX(Number) Number FROM ( SELECT Number 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 ) )r WHERE @ % Number = 0 -- ```

Other Prime Number related SQL Puzzle links-

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