# Write a program in TSQL to find the largest prime among a collection of numbers Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 703
Let's say that we have a table of integers as {104723, 104729, 9998,448 }. Objective is to find the largest prime among a collection of numbers. The below program will do so

`Declare @t table(number int)Insert into @t values(104723),(104729),(9998),(448)Declare @maxNum INTSELECT @maxNum = MAX(number) FROM @t--generate a number table from 2 to the maximum number supplied;WITH Cte AS (    SELECT 2 AS num 		UNION ALL    SELECT num+1 	FROM cte 	WHERE num<@maxNum)SELECT TOP(1) num AS 'Largest Prime' FROM cte--filter by some known prime numbers (keeping the range between 2 to 19WHERE 	(num=2 OR num%2!=0) AND    (num=3 OR num%3!=0) AND    (num=5 OR num%5!=0) AND    (num=7 OR num%7!=0) AND    (num=11 OR num%11!=0) AND	(num=13 OR num%13!=0) AND	(num=17 OR num%17!=0) AND	(num=19 OR num%19!=0) ORDER BY 1 DESCOPTION (MAXRECURSION 0)/*Largest Prime-------------104729*/`

We are first generating a number table from 2 to the largest number in the table (i.e. 104729) by using recursive CTE and then filtering that with the know prime numbers between the range 2 to 19.
Finally we are sorting the records and displaying the top most record.