December 12, 2017 at 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 ?
December 12, 2017 at 6:09 am
Thom A - Tuesday, December 12, 2017 4:59 AMunas_sasing - Tuesday, December 12, 2017 4:32 AMfrom 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.
December 12, 2017 at 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
First solve the problem then write the code !
December 12, 2017 at 6:14 am
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
December 12, 2017 at 8:53 am
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!
December 12, 2017 at 3:56 pm
unas_sasing - Tuesday, December 12, 2017 4:32 AMDear all,I have SP :
CREATE proc [dbo].[usp_FindPrimes] (@opt tinyint, @ResultCount int)
as
set ROWCOUNT @ResultCountdeclare @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 tableif exists (select * from sys.objects where type = 'u' and name = 'Primes')
drop table PrimesCREATE 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)
endselect @NextInt = max(Prime_No) + 2
from Primesset @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
endset @NextInt = @NextInt + 2
endif @opt = 1
select * from Primes ORDER BY Prime_No asc
else if @opt = 2
select * from Primes ORDER BY Prime_No descreturn
--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
29from 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
Change is inevitable... Change for the better is not.
December 12, 2017 at 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..
December 13, 2017 at 5:27 am
unas_sasing - Tuesday, December 12, 2017 7:54 PMHai 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
Change is inevitable... Change for the better is not.
December 13, 2017 at 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
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2017 at 5:57 am
Jeff Moden - Wednesday, December 13, 2017 5:27 AMunas_sasing - Tuesday, December 12, 2017 7:54 PMHai 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
December 13, 2017 at 6:05 am
Jeff Moden - Wednesday, December 13, 2017 5:47 AMTheCTEGuy - 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 descThat 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 !
December 13, 2017 at 6:08 am
Thank you both for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2017 at 2:18 pm
Jeff Moden - Wednesday, December 13, 2017 5:27 AMunas_sasing - Tuesday, December 12, 2017 7:54 PMHai 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
December 13, 2017 at 3:18 pm
Jason A. Long - Wednesday, December 13, 2017 2:18 PMJeff Moden - Wednesday, December 13, 2017 5:27 AMunas_sasing - Tuesday, December 12, 2017 7:54 PMHai 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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply