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 INT
SELECT @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 19
WHERE
(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 DESC
OPTION (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.