PRIME NUMBERS

  • 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 ?

  • 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

  • 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.

  • 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 !

  • 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

  • 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!

  • 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)

  • 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..

  • 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)

  • 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)

  • 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

  • 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 !

  • 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)

  • 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

  • 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)

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply