Tags

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


SQL Puzzle | The Prime Number Puzzle – V

This is a variation in the Prime Puzzle . Here you have to return a list all prime numbers above a given number of N. And of course we cannot use any of the existing methods. 🙂

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

Other Prime Number related 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

Sample Input

DECLARE @PrimeNumbersBelow AS INT = 1000 ( Prime Numbers below this number )

Expected Output

Number
2
3
5
7
11
13
17
19
23
29
31
37
41
43
47
53
59
61
67
71
73
79
83
89
97
101
103
107
109
113
127
131
137
139
149
151
157
163
167
173
179
181
191
193
197
199
211
223
227
229
233
239
241
251
257
263
269
271
277
281
283
293
307
311
313
317
331
337
347
349
353
359
367
373
379
383
389
397
401
409
419
421
431
433
439
443
449
457
461
463
467
479
487
491
499
503
509
521
523
541
547
557
563
569
571
577
587
593
599
601
607
613
617
619
631
641
643
647
653
659
661
673
677
683
691
701
709
719
727
733
739
743
751
757
761
769
773
787
797
809
811
821
823
827
829
839
853
857
859
863
877
881
883
887
907
911
919
929
937
941
947
953
967
971
977
983
991
997

Solution 1


--


;WITH CTE AS
(
	SELECT DISTINCT Number FROM Master..Spt_Values WHERE Number > 1 AND Number  <= 1000
)	
SELECT * 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
) 
ORDER BY number


--

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