Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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-

Sr No SQL Server Puzzle
1 SQL Puzzle | 5 Prime Numbers above a given number
2 SQL Puzzle | The Circular Prime Numbers Puzzle
3 SQL Puzzle | Check Prime Number Puzzle
4 SQL Puzzle | The Prime Numbers Puzzle
5 SQL Puzzle | The Prime Number Puzzle – V

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

Advertisements