PRIME NUMBERS

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  December 12, 2017 at 4:32 am

  #341226

  Dear all,

  I have SP :

  
CREATE proc [dbo].[usp_FindPrimes] (@opt tinyint, @ResultCount int)
as
set ROWCOUNT @ResultCount

  declare @NextInt int--This is the next number to check
declare @Count int--Count how many primes to find
declare @BaseCount int--Used to initially check the table

  if exists (select * from sys.objects where type = 'u' and name = 'Primes')
drop table Primes

  CREATE TABLE [dbo].[Primes](
    [Prime_No] [int] NOT NULL,
CONSTRAINT [PK_Prime] PRIMARY KEY CLUSTERED
(
    [Prime_No] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Insert into Primes (Prime_No) values (2)
Insert into Primes (Prime_No) values (3)

  truncate table PRIMES

  Select @BaseCount = count(Prime_No) from primes where Prime_No in (2,3)
If @BaseCount <> 2 begin
    Delete from primes where Prime_No in (2,3)
    insert into primes (Prime_No) values (2)
    insert into primes (Prime_No) values (3)
end

  select @NextInt = max(Prime_No) + 2
from Primes

  set @Count = 0

  while @Count < 5000 begin

      if not exists(
        Select Prime_No
        from Primes
        where @NextInt % Prime_No = 0
            and sqrt(@NextInt) >= Prime_No)
    begin
        insert into Primes(Prime_No) select @NextInt
        set @Count = @Count + 1
    end

      set @NextInt = @NextInt + 2
end

  if @opt = 1
select * from Primes ORDER BY Prime_No asc
else if @opt = 2
select * from Primes ORDER BY Prime_No desc

  return

  --EXAMPE EXECUTE:
--exec usp_FindPrimes 1 /*choose how the Primes Show asc)*/,10 /*input the max ROW*/

  --THE RESULT (if I choose @opt = 1:
Prime_No
2
3
5
7
11
13
17
19
23
29

  from 2-29, how to make the opposite become 29-2 ?

• Thom A

  SSC Guru

  Points: 99130

  More actions

  December 12, 2017 at 4:59 am

  #1971712

  unas_sasing - Tuesday, December 12, 2017 4:32 AM

  from 2-29, how to make the opposite become 29-2 ?

  Could you rephrase your question?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  December 12, 2017 at 6:09 am

  #1971726

  Thom A - Tuesday, December 12, 2017 4:59 AM

  unas_sasing - Tuesday, December 12, 2017 4:32 AM

  from 2-29, how to make the opposite become 29-2 ?

  Could you rephrase your question?

  Sounds like the OP wants the result set returned in the inverse order from the asc output.

  The proc generates a result set 2,3,5,7,11,13,17,19,23,29, but would like it to be returned 29,23,19,17,13,11,7,5,3,2 instead.

• TheCTEGuy

  Ten Centuries

  Points: 1032

  More actions

  December 12, 2017 at 6:13 am

  #1971727

  USE test;
  GO
  /*Tally table is the table with single column having no.s 1to N */

  SELECT * FROM (
  SELECT p.n,SUM(p.count) AS final
  FROM dbo.tally a
  CROSS APPLY (SELECT a.n,CASE WHEN (a.n%(b.n)) = 0 THEN 1 ELSE 0 END AS count FROM dbo.tally b WHERE a.N > b.n) AS p
  WHERE a.n < 30
  GROUP BY p.N
  )AS a
  WHERE a.final = 1
  order by n desc

  First solve the problem then write the code !

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  December 12, 2017 at 6:14 am

  #1971728

  My way would be to add option 3 as below

  
CREATE proc [dbo].[usp_FindPrimes] (@opt tinyint, @ResultCount int)
AS
declare @NextInt int--This is the next number to check
declare @Count int--Count how many primes to find
declare @BaseCount int--Used to initially check the table

  if exists (select * from sys.objects where type = 'u' and name = 'Primes')
drop table Primes

  CREATE TABLE [dbo].[Primes](
[Prime_No] [int] NOT NULL,
CONSTRAINT [PK_Prime] PRIMARY KEY CLUSTERED
(
[Prime_No] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Insert into Primes (Prime_No) values (2)
Insert into Primes (Prime_No) values (3)

  truncate table PRIMES

  Select @BaseCount = count(Prime_No) from primes where Prime_No in (2,3)
If @BaseCount <> 2 begin
Delete from primes where Prime_No in (2,3)
insert into primes (Prime_No) values (2)
insert into primes (Prime_No) values (3)
end

  select @NextInt = max(Prime_No) + 2
from Primes

  set @Count = 0

  while @Count < 5000 begin

  if not exists(
Select Prime_No
from Primes
where @NextInt % Prime_No = 0
and sqrt(@NextInt) >= Prime_No)
begin
insert into Primes(Prime_No) select @NextInt
set @Count = @Count + 1
end

  set @NextInt = @NextInt + 2
end

  if @opt = 1
select top (@ResultCount) * from Primes ORDER BY Prime_No asc
else if @opt = 2
select top (@ResultCount)  * from Primes ORDER BY Prime_No desc
else if @opt = 3
begin
select top (@ResultCount) * INTO #Temp FROM Primes ORDER BY Prime_No asc
SELECT * FROM #Temp ORDER BY Prime_No desc
end

  return
go


  Then execute
  
exec usp_findprimes 3,10


• tbailey 19088

  SSCrazy

  Points: 2317

  More actions

  December 12, 2017 at 8:53 am

  #1971769

  For minimal change from the existing procedure, I'd do something like this for the final queries:
  if @opt = 1
    SELECT t.Prime_No
    FROM (select ROW_NUMBER() OVER(ORDER BY Prime_No ASC) rowNo, Prime_No from Primes) t
    WHERE t.rowNo <= @ResultCount
    ORDER BY Prime_No ASC
  else if @opt = 2
    SELECT Prime_No
    FROM (select ROW_NUMBER() OVER(ORDER BY Prime_No ASC) rowNo, Prime_No from Primes) t
    WHERE t.rowNo <= @ResultCount
    ORDER BY Prime_No DESC

  Good luck!

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 12, 2017 at 3:56 pm

  #1971858

  unas_sasing - Tuesday, December 12, 2017 4:32 AM

  Dear all,

  I have SP :

  
CREATE proc [dbo].[usp_FindPrimes] (@opt tinyint, @ResultCount int)
as
set ROWCOUNT @ResultCount

  declare @NextInt int--This is the next number to check
declare @Count int--Count how many primes to find
declare @BaseCount int--Used to initially check the table

  if exists (select * from sys.objects where type = 'u' and name = 'Primes')
drop table Primes

  CREATE TABLE [dbo].[Primes](
    [Prime_No] [int] NOT NULL,
CONSTRAINT [PK_Prime] PRIMARY KEY CLUSTERED
(
    [Prime_No] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Insert into Primes (Prime_No) values (2)
Insert into Primes (Prime_No) values (3)

  truncate table PRIMES

  Select @BaseCount = count(Prime_No) from primes where Prime_No in (2,3)
If @BaseCount <> 2 begin
    Delete from primes where Prime_No in (2,3)
    insert into primes (Prime_No) values (2)
    insert into primes (Prime_No) values (3)
end

  select @NextInt = max(Prime_No) + 2
from Primes

  set @Count = 0

  while @Count < 5000 begin

      if not exists(
        Select Prime_No
        from Primes
        where @NextInt % Prime_No = 0
            and sqrt(@NextInt) >= Prime_No)
    begin
        insert into Primes(Prime_No) select @NextInt
        set @Count = @Count + 1
    end

      set @NextInt = @NextInt + 2
end

  if @opt = 1
select * from Primes ORDER BY Prime_No asc
else if @opt = 2
select * from Primes ORDER BY Prime_No desc

  return

  --EXAMPE EXECUTE:
--exec usp_FindPrimes 1 /*choose how the Primes Show asc)*/,10 /*input the max ROW*/

  --THE RESULT (if I choose @opt = 1:
Prime_No
2
3
5
7
11
13
17
19
23
29

  from 2-29, how to make the opposite become 29-2 ?

  Ummm... EXEC with option 2 instead of option 1 as it is documented in the code???

  Shifting gears a bit, if you need to use Prime Numbers, stop recalculating them every time.  Create a permanent "Helper Table" as you would with a Tally table and simply select from it.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  December 12, 2017 at 7:54 pm

  #1971875

  Hai all,

  thank you for helping me,

  Yes I would like to reverse from a result set 2,3,5,7,11,13,17,19,23,29, would like to be returned 29,23,19,17,13,11,7,5,3,2 instead. 

  and perhaps I've got the answer from @anthony.green and @tbailey 19088.

  Thank you very much.

  I will try the Syntax..

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 13, 2017 at 5:27 am

  #1971927

  unas_sasing - Tuesday, December 12, 2017 7:54 PM

  Hai all,

  thank you for helping me,

  Yes I would like to reverse from a result set 2,3,5,7,11,13,17,19,23,29, would like to be returned 29,23,19,17,13,11,7,5,3,2 instead. 

  and perhaps I've got the answer from @anthony.green and @tbailey 19088.

  Thank you very much.

  I will try the Syntax..

  I've not executed it but the original code will do it.  Instead of calling it with a 1, just call it with a 2.  And, seriously, instead of calling a proc every time you need Prime Numbers, which don't change, use the proc just once to build a table of Prime Numbers.  Your code that needs the prime numbers will be faster and easier to write.

  If you do insist on using a proc for this instead of such a "Helper Table", at least find a Prime Number generator that will be fast.  Use Peter "Peso" Larsson's code from the following link.  It will generate prime numbers up to 1,000,000 in only 2 to 4 seconds depending on your machine and cache.  It's the one that "Peso" posted with "This is about as easy and fast as possible, set-based" in it.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 13, 2017 at 5:47 am

  #1971930

  TheCTEGuy - Tuesday, December 12, 2017 6:13 AM

  USE test;
  GO
  /*Tally table is the table with single column having no.s 1to N */

  SELECT * FROM (
  SELECT p.n,SUM(p.count) AS final
  FROM dbo.tally a
  CROSS APPLY (SELECT a.n,CASE WHEN (a.n%(b.n)) = 0 THEN 1 ELSE 0 END AS count FROM dbo.tally b WHERE a.N > b.n) AS p
  WHERE a.n < 30
  GROUP BY p.N
  )AS a
  WHERE a.final = 1
  order by n desc

  That works but be very aware that it's as slow as a While loop for small stuff and will take an insane number of internal resources and time even for values of N as little a 11,000 because it has a "Triangular Join" built into the correlation provided by the CROSS APPLY.  For more information on what a "Triangular Join" is, please see the following article...
  Hidden RBAR: Triangular Joins

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  December 13, 2017 at 5:57 am

  #1971931

  Jeff Moden - Wednesday, December 13, 2017 5:27 AM

  unas_sasing - Tuesday, December 12, 2017 7:54 PM

  Hai all,

  thank you for helping me,

  Yes I would like to reverse from a result set 2,3,5,7,11,13,17,19,23,29, would like to be returned 29,23,19,17,13,11,7,5,3,2 instead. 

  and perhaps I've got the answer from @anthony.green and @tbailey 19088.

  Thank you very much.

  I will try the Syntax..

  I've not executed it but the original code will do it.  Instead of calling it with a 1, just call it with a 2.  And, seriously, instead of calling a proc every time you need Prime Numbers, which don't change, use the proc just once to build a table of Prime Numbers.  Your code that needs the prime numbers will be faster and easier to write.

  If you do insist on using a proc for this instead of such a "Helper Table", at least find a Prime Number generator that will be fast.  Use Peter "Peso" Larsson's code from the following link.  It will generate prime numbers up to 1,000,000 in only 2 to 4 seconds depending on your machine and cache.  It's the one that "Peso" posted with "This is about as easy and fast as possible, set-based" in it.

  allright Jeff,

  thank you so much for the advice, I really appreciate it

• TheCTEGuy

  Ten Centuries

  Points: 1032

  More actions

  December 13, 2017 at 6:05 am

  #1971933

  Jeff Moden - Wednesday, December 13, 2017 5:47 AM

  TheCTEGuy - Tuesday, December 12, 2017 6:13 AM

  USE test;
  GO
  /*Tally table is the table with single column having no.s 1to N */

  SELECT * FROM (
  SELECT p.n,SUM(p.count) AS final
  FROM dbo.tally a
  CROSS APPLY (SELECT a.n,CASE WHEN (a.n%(b.n)) = 0 THEN 1 ELSE 0 END AS count FROM dbo.tally b WHERE a.N > b.n) AS p
  WHERE a.n < 30
  GROUP BY p.N
  )AS a
  WHERE a.final = 1
  order by n desc

  That works but be very aware that it's as slow as a While loop for small stuff and will take an insane number of internal resources and time even for values of N as little a 11,000 because it has a "Triangular Join" built into the correlation provided by the CROSS APPLY.  For more information on what a "Triangular Join" is, please see the following article...
  Hidden RBAR: Triangular Joins

  Excellent Jeff - An eye opener 🙂
  Added to briefcase !

  First solve the problem then write the code !

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 13, 2017 at 6:08 am

  #1971935

  Thank you both for the feedback.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  December 13, 2017 at 2:18 pm

  #1972034

  Jeff Moden - Wednesday, December 13, 2017 5:27 AM

  unas_sasing - Tuesday, December 12, 2017 7:54 PM

  Hai all,

  thank you for helping me,

  Yes I would like to reverse from a result set 2,3,5,7,11,13,17,19,23,29, would like to be returned 29,23,19,17,13,11,7,5,3,2 instead. 

  and perhaps I've got the answer from @anthony.green and @tbailey 19088.

  Thank you very much.

  I will try the Syntax..

  I've not executed it but the original code will do it.  Instead of calling it with a 1, just call it with a 2.  And, seriously, instead of calling a proc every time you need Prime Numbers, which don't change, use the proc just once to build a table of Prime Numbers.  Your code that needs the prime numbers will be faster and easier to write.

  If you do insist on using a proc for this instead of such a "Helper Table", at least find a Prime Number generator that will be fast.  Use Peter "Peso" Larsson's code from the following link.  It will generate prime numbers up to 1,000,000 in only 2 to 4 seconds depending on your machine and cache.  It's the one that "Peso" posted with "This is about as easy and fast as possible, set-based" in it.

  Unless you're in the business of discovering new primes, don't bother doing any prime calculations at all... The interwebs has no shortage of list of pre-calculated primes.
  This site alone covers you from 0 to 1,000,000,000,000... http://www.primos.mat.br/indexen.html

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 13, 2017 at 3:18 pm

  #1972049

  Jason A. Long - Wednesday, December 13, 2017 2:18 PM

  Jeff Moden - Wednesday, December 13, 2017 5:27 AM

  unas_sasing - Tuesday, December 12, 2017 7:54 PM

  Hai all,

  thank you for helping me,

  Yes I would like to reverse from a result set 2,3,5,7,11,13,17,19,23,29, would like to be returned 29,23,19,17,13,11,7,5,3,2 instead. 

  and perhaps I've got the answer from @anthony.green and @tbailey 19088.

  Thank you very much.

  I will try the Syntax..

  I've not executed it but the original code will do it.  Instead of calling it with a 1, just call it with a 2.  And, seriously, instead of calling a proc every time you need Prime Numbers, which don't change, use the proc just once to build a table of Prime Numbers.  Your code that needs the prime numbers will be faster and easier to write.

  If you do insist on using a proc for this instead of such a "Helper Table", at least find a Prime Number generator that will be fast.  Use Peter "Peso" Larsson's code from the following link.  It will generate prime numbers up to 1,000,000 in only 2 to 4 seconds depending on your machine and cache.  It's the one that "Peso" posted with "This is about as easy and fast as possible, set-based" in it.

  Unless you're in the business of discovering new primes, don't bother doing any prime calculations at all... The interwebs has no shortage of list of pre-calculated primes.
  This site alone covers you from 0 to 1,000,000,000,000... http://www.primos.mat.br/indexen.html

  'Zactly but the problem with WebSites like that is that you have to unzip multiple files to get what you need.  If you use Peter "Peso" Larsson's code that I provided at the link a bit above, it'll only take a couple of seconds instead of downloading, unpacking, importing, etc.  It takes 2-4 seconds for Peter's code to do the Primes up to 1,000,000 and is pretty linear and I've tested it at 20-40 seconds for the first 10,000,000.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)