Results 1 to 12 of 12

070110, 07:41 #1King of Understatement
 Join Date
 Feb 2004
 Location
 One Flump in One Place
 Posts
 14,912
Unanswered: Mathematics  algorithm to find greatest exponent for a base
Hi
A rather humbling experience for your favourite flump. I have a task that requires finding the greatest exponent a base can be raised by without the result passing a threshold (in this case the maximum BIGINT). I have ended up writing the below simple SQL to generate a table that I can use to look up the exponents.
However I am sure there must be a better way, ideally one that fits in with the below criteria:
 The value will be calculated at run time
 The @base will be a fixed and known BIGINT with a value between 2 and 36
 The algorithm will not rely on errors to work
 Ideally it would make use of a numbers table (since this would then integrate nicely in to an inline function I have written)
Code:/* Find the greatest exponent of a base that can exist in an 8 byte integer */ SET NOCOUNT ON DECLARE @base AS BIGINT = 2 Start with binary , @exponent AS TINYINT = 62 We know 62 is the greatest exponent for binary so start with this , @pow AS BIGINT Scratch variable Table for results DECLARE @powers TABLE ( base TINYINT NULL , max_exponent TINYINT NULL ) Work through to base 36 WHILE @base <= 36 BEGIN Use TRY...CATCH as a fudge to identify greatest exponent BEGIN TRY Attempt to raise base to the exponent Error caught if arithmetic overflow SELECT @pow = POWER(@base, @exponent) If we get here then this is the max exponent  log it INSERT INTO @powers VALUES (@base, @exponent) Move to next base SELECT @base += 1 END TRY BEGIN CATCH Exponent is too high  deduct one and try again SELECT @exponent = 1 END CATCH END SELECT * FROM @powers

070110, 09:51 #2Registered User
 Join Date
 Jan 2003
 Location
 Massachusetts
 Posts
 5,842
Provided Answers: 16Suppose you take the threshold value (2^64  1, but you can reconfigure if you like), and divide by the base. This would become your 'lower threshold", because if you multiply any number above that threshold by the base, you will get an overflow. So all you need is to increase the exponent, until you break the lower threshold. Saves you the catch. As for doing this outside of a while loop, I am not sure.
Also, POWER seems to return the datatype of the base. You will want to declare that as a bigint.

070110, 10:10 #3King of Understatement
 Join Date
 Feb 2004
 Location
 One Flump in One Place
 Posts
 14,912
Ok, so this works:
Code:DECLARE @base AS BIGINT = 36 SELECT max_exponent = MAX(number) FROM dbo.numbers WHERE number <= 62 AND POWER(CAST(@base AS FLOAT), number) <= CAST(0x7FFFFFFFFFFFFFFF AS BIGINT)

070110, 10:12 #4King of Understatement
 Join Date
 Feb 2004
 Location
 One Flump in One Place
 Posts
 14,912

070110, 10:53 #5www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,444
Provided Answers: 12

070110, 10:56 #6Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54I'd cheat and use basic mathematics for this one.
Code: ptp 20100701 Show maximum exponent for a given base that does not exceed specified limit DECLARE @limit BIGINT , @base BIGINT SET @limit = 100 SET @base = 2 SELECT LOG(@limit) / LOG(@base)
In theory, theory and practice are identical. In practice, theory and practice are unrelated.

070110, 11:09 #7King of Understatement
 Join Date
 Feb 2004
 Location
 One Flump in One Place
 Posts
 14,912
Yay Pat!
Code:/* Find the greatest exponent of a base that can exist in an 8 byte integer */ SET NOCOUNT ON DECLARE @base AS BIGINT = 2 Start with binary , @exponent AS TINYINT = 62 We know 62 is the greatest exponent for binary so start with this , @pow AS BIGINT Scratch variable Table for results DECLARE @powers TABLE ( base TINYINT NULL , max_exponent TINYINT NULL ) Work through to base 36 WHILE @base <= 36 BEGIN Use TRY...CATCH as a fudge to identify greatest exponent BEGIN TRY Attempt to raise base to the exponent Error caught if arithmetic overflow SELECT @pow = POWER(@base, @exponent) If we get here then this is the max exponent  log it INSERT INTO @powers VALUES (@base, @exponent) Move to next base SELECT @base += 1 END TRY BEGIN CATCH Exponent is too high  deduct one and try again SELECT @exponent = 1 END CATCH END DECLARE @limit BIGINT SET @limit = CAST(0x7FFFFFFFFFFFFFFF AS BIGINT) SELECT * , FLOOR(CAST(LOG(@limit) AS DECIMAL(38, 36)) / CAST(LOG(base) AS DECIMAL(38, 36))) FROM @powers
Many thanks

070110, 11:10 #8King of Understatement
 Join Date
 Feb 2004
 Location
 One Flump in One Place
 Posts
 14,912

070110, 11:32 #9www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,444
Provided Answers: 12

070110, 11:32 #10Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54

070110, 13:16 #11www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,444
Provided Answers: 12

070110, 13:22 #12Registered User
 Join Date
 Jan 2003
 Location
 Massachusetts
 Posts
 5,842
Provided Answers: 16What? Is she that hot?