June 24, 2016 at 4:14 am
hi friends ,
if I give input for @a is 7,8,10,11,12,13 and ect . I will get perfect answer .
but when will I give input for @a is 9 or 4 , I did not get answer. pls try to solve
codings : check value is prime or not
declare @a int = 13
declare @b-2 int = 1
declare @b1 int = 0
while(@a >= @b-2)
begin
if (@a%@b = 0)
begin
set @b1 = @b1 + 1
end
else if (@b1 > 2)
begin
print 'The value is not prime'
break
end
end
-- PRINT @B1
-- PRINT @B-1
if (@b1 = 2) AND (@b-1 = @a)
print 'The value is prime'
June 24, 2016 at 5:45 am
Here is a prime number snippet I wrote a while ago, should help you getting started.
😎
USE tempdb;
GO
SET NOCOUNT ON;
--DECLARE @INT_BUCKET_01 INT = 0;
DECLARE @NUMBER INT = 1000000;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(CONVERT(INT,CEILING(@NUMBER/6.0),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
, TWINS(N) AS (SELECT -1 UNION ALL SELECT 1)
,SMALL_PRIMES AS (SELECT N FROM (VALUES (1),(2),(3),(5),(7),(11),(13),(17),(19),(23),(29),(31),(37),(41),(43),(47),(53),(59)) AS X(N))
,SIEVEX(N) AS
(
SELECT
((NM.N * 6) + TW.N) AS N
FROM NUMS NM
CROSS APPLY TWINS TW
)
,FIRST_FILTER AS
(
SELECT
SX.N
FROM SIEVEX SX
WHERE 0 NOT IN (SX.N % 3,SX.N % 5,SX.N % 7,SX.N % 11,SX.N % 13,SX.N % 17,SX.N % 19,SX.N % 23,SX.N % 29,SX.N % 31,SX.N % 37,SX.N % 41,SX.N % 43,SX.N % 47,SX.N % 53,SX.N % 59)
AND SX.N > 59
AND SQRT(SX.N) - FLOOR(SQRT(SX.N)) > 0.0
)
,PRE_SELECT AS
(
SELECT
FF.N AS N
,COUNT(*) OVER (PARTITION BY FF.N) AS DIV_CNT
FROM FIRST_FILTER FF
CROSS APPLY SMALL_PRIMES AS NR
WHERE FF.N % NR.N = 0
)
,PRIME_SELECT AS
(
SELECT
PS.N
,COUNT(*) OVER (PARTITION BY PS.N) AS DIV_CNT
,NR.N AS XN
FROM PRE_SELECT PS
CROSS APPLY
(
SELECT 1 AS N UNION ALL
SELECT
TOP(CONVERT(INT,(CEILING(SQRT(PS.N)) / 2),0)) 1 + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) * 2) AS N
FROM T T1,T T2,T T3,T T4,T T5
) AS NR
WHERE PS.N % NR.N = 0
AND PS.DIV_CNT = 1
)
,PRIMES AS
(
SELECT
SP.N
FROM SMALL_PRIMES SP
WHERE SP.N > 1
UNION ALL
SELECT
PS.N
FROM PRIME_SELECT PS
WHERE PS.DIV_CNT < 2
)
SELECT
--@INT_BUCKET_01 =
P.N
FROM PRIMES P;
--SELECT @INT_BUCKET_01;
June 24, 2016 at 6:48 am
Thank u very much Eirikur Eiriksson . your code is working perfectly. really its awesome.
could u please suggest me .. in your free time ....,
what is the mistake in my coding.., my simple coding was working well.. but while giving input as 4 , 9 that time its not working.
June 24, 2016 at 7:13 am
Your first problem is to use SQL code as VB code.
The second problem is that you have an ELSE which you shouldn't have.
Here's an alternative, which is simpler than Eirikur's code (which is amazing by the way).
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT CASE WHEN EXISTS (SELECT *
FROM cteTally
WHERE @a % n = 0
AND n > 1
AND n < @a)
THEN 'The value is not prime'
ELSE 'The value is prime' END
Just to explain, the first part (before SELECT CASE) is just making a table with numbers. It's known as tally table and its logic will help you a lot in SQL. Google about it and be sure to read this article: http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
June 24, 2016 at 7:56 am
Thank u very much Luis Cazares for your valuable time. your code is working awesome.
as per your suggestion I refer 'http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/' site also. its very useful to me . thank u very much.
June 24, 2016 at 8:03 am
Anandkumar-SQL_Developer (6/24/2016)
Thank u very much Luis Cazares for your valuable time. its working.
The question now is. Do you understand it? Would you be able to explain it to someone else if needed? Would you be able to trouble shoot it?
June 24, 2016 at 8:29 am
Yeah ,
of course Luis Cazares , surely I will do here after .
June 24, 2016 at 9:20 am
Here is something I did some time ago.
Little bit loopdy loop, but returns primenumber list from first million integers in about 12 seconds on my server due to optimised incrementing and early exit.
All PrimerNumbers where the number > 10 End in 1379.
Never a 5, so we can skip that.
Also, dont increment by 1.
That is twice as wasteful since half the numbers are divisible by 2.
Increment in 2 unless you are at 3, then increment by 4, since we want to skip numbers ending in 5's.
Also, Stop checking factors when you get to the threshold where the factor > Squaroot of the number you checking.
Since, if TestNumber = A*B, and A!=B, THEN there must exists a A< sqrt(TestNumber) AND B > sqrt(TestNumber).
If you cant a factor A that exists and is less than sqrt(TestNumber) THEN B does not exist either, so stop looking.
So early exit.
set nocount on
Declare @PrimeList table(PrimeNumber INT primary KEY CLustered)
Declare @TestValue INT =9
Declare @FinalValue INT = 1000000
declare @IterationNumber tinyint = 4
Insert into @primelist (Primenumber)select (2) as Prime union all select 3 union all select 5 union all select 7;
While (@TestValue<@finalValue)
BEGIN
if not exists(select @testvalue%PrimeNumber,@testvalue,PrimeNumber
from @primelist
Where PrimeNumber< sqrt(1.0*@TestValue+1) and @testvalue%PrimeNumber =0) -- If A > root of the Testvalue, then there exists another factor less than the root.
--Therefore, if you get to the squareroot without getting a factor, that implies that no factor exists which is more than the squareroot,
--SOOOOO Early Exit
Insert into @primelist (Primenumber)select @testvalue as Prime
Set @TestValue = (case WHEN @IterationNumber in(1,3,4) THEN @TestValue+2 ELSE @TestValue+4 END) --Skip 5's
set @IterationNumber = (case WHEN @IterationNumber <4 THEN @IterationNumber+1 Else 1 END)
END
SELECT * from @primelist order by 1
A number is prime if it does not have prime factors.
So no point dividing by 999 if it is not divisible by 3, which we already know.
If it is not divisible by3, then it is not divisible by 9 or 111 since 3 is a factor of those.
So when we check prime numbers, we only divide by primenumbers, not any number.
June 24, 2016 at 10:29 am
I think that Luis' solution will perform faster than any looping solution, especially once the square root short circuit is added.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 24, 2016 at 3:06 pm
Withdrawn for unforgivably not seeing Luis' answer and writing the same thing myself. :w00t:
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 24, 2016 at 4:20 pm
drew.allen (6/24/2016)
I think that Luis' solution will perform faster than any looping solution, especially once the square root short circuit is added.Drew
surely the cte is faster, but they do different things. Luis solution returns whether a given number is prime whereas mine returns a list of about 79000 prime numbers from the first million integers.
Though my example is probably beyond the pale as my project was to see how long it took to build the list, which in 1997 took an age and a day with C, so the Math had to be clearly understood to make it work quickly by working out shortcuts.
June 24, 2016 at 6:08 pm
MadAdmin (6/24/2016)
drew.allen (6/24/2016)
I think that Luis' solution will perform faster than any looping solution, especially once the square root short circuit is added.Drew
surely the cte is faster, but they do different things. Luis solution returns whether a given number is prime whereas mine returns a list of about 79000 prime numbers from the first million integers.
Though my example is probably beyond the pale as my project was to see how long it took to build the list, which in 1997 took an age and a day with C, so the Math had to be clearly understood to make it work quickly by working out shortcuts.
In that case, Eirikur's code would do the same thing.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply