Home 2017 June SQL Puzzle: SQL Advance Query – Find first 100 Prime numbers in SQL Server

SQL Puzzle: SQL Advance Query – Find first 100 Prime numbers in SQL Server

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Question:
Write a SQL query to find first 100 Prime numbers in SQL Server.

Require Output:

Solution:

Anvesh Patel
SQL Puzzle: SQL Advance Query - Draw a Triangle without using any LoopPostgreSQL: Use PSQL command line variable to make your SQL Queries Dynamic
Comments: 5
  1. Pasi
    September 22, 2017 at 6:49 pm

    Incomplete solution, that’s only 25 first prime numbers… šŸ˜‰

    ReplyCancel
  2. Ankush
    September 17, 2018 at 11:26 am

    Select * from (
    Select rw, case when (mod(rw,2) 0 OR rw= 2) AND
    (mod(rw,3) 0 OR rw = 3) AND
    (mod(rw,5) 0 OR rw = 5) AND
    mod(rw,9) 0 AND
    mod(rw,11) 0 THEN ‘Prime’ ELSE ‘Not Prime’ END as Number_Status FROM
    (Select Rownum as rw
    From dual
    Connect By Rownum <= 1000)foo)foo2
    where Number_Status = 'Prime'
    and rownum <= 100

    ReplyCancel
  3. Carter
    June 29, 2019 at 10:00 pm

    You only showing the prime number from 1 to 100
    Wh as version of SQL are you using because Mod is build in function in all SQL

    For me would use a CTE table and then select from the CTE table and cross join it to it self

    ;with CTEPrime(
    Select 1 n
    Union all
    Select n+1 from CTEPrime
    Where nc2.n

    ReplyCancel
  4. Ashish Rawat
    October 4, 2019 at 2:37 pm

    Easiest and fastest way to find primes numbers for first 100 numbers in SQL server

    DECLARE @range int = 100 –(Select your range )
    ,@x INT = 1, @y INT = 1

    While (@y <= @range)
    BEGIN
    while (@x <= @y)
    begin
    IF ((@y%@x) =0)
    BEGIN
    IF (@x = @y)
    PRINT @y
    break
    END
    IF ((@y%@x)0)
    set @x = @x+1
    end
    set @x = 2
    set @y = @y+1
    end

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

June 18, 2017 5 Comments SQL Puzzle, , , , , , , , , , , , , ,