19
nibor
5y

Inspired by this post
https://devrant.com/rants/2217978/...

I challenged myself to use SQL to get the prime numbers under 100,000

Comments
  • 3
    CREATE TABLE #OddNumbers (Number INT NOT NULL);

    -- Populate with odd numbers >= 3
    INSERT #OddNumbers (Number)
    SELECT TOP (500000)
    (ROW_NUMBER() OVER (ORDER BY t1.number) - 1) * 2 + 3 AS N2
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2;

    -- Add a primary key
    ALTER TABLE #OddNumbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

    -- Select 2 as a special cae
    SELECT 2 AS PrimeNumber
    UNION
    -- Select all numbers from #OddNumbers that do not have a divisor > 1 and < half the number being tested
    SELECT testedNumber.Number AS PrimeNumber
    FROM #OddNumbers testedNumber
    LEFT JOIN #OddNumbers divisor
    ON divisor.Number < (testedNumber.Number / 2) AND testedNumber.Number % divisor.Number = 0
    GROUP BY testedNumber.Number
    HAVING COUNT(divisor.Number) = 0;

    DROP TABLE #OddNumbers
  • 1
  • 4
    So you went to such lengths with SQL, but couldn't make a proper screenshot?
  • 2
    @kamen yep, cos I can't access devrant from my work machine (locked down)
  • 0
    I thought it was a useful exercise to make sure my brain hasn't been turned into mush by all the dev ops stuff I've been working on recently
  • 0
    so... how fast is it?
  • 1
    @git-gud it took 1hr 36mins to do upto 1,000,000
  • 0
Add Comment