Post date: Jul 09, 2014 12:52:34 AM
"How many distinct results are there in the series a ** b, where a and b are both made of up of the integers between 2 and 100?"
This would be very easy to do, a simple SELECT DISTINCT with a Cartesian join, except that once we get over 26 ** 26 we start hitting numbers too big for SQL Server. The size of the numbers makes it tricky. But rather than implement an exponential function for giant numbers, focus on expressing numbers as the smallest number possible to the power of another number, and find the unique combinations. Remember that 9 ** 2 is the same as 3 ** 4, 27 ** 4 is 3 ** 12, etc. Take the square root of the first part and multiply the second by 2, or take the cube root of the first part and multiply the second by 3, etc.
-- It is important that the datatype include decimals. This saves us from having to convert everything during the division step later.
CREATE TABLE #Integers (i numeric(19,4) PRIMARY KEY NOT NULL)
; WITH Integers (i) As
(
SELECT 2
UNION ALL
SELECT i + 1
FROM Integers
WHERE i < 100
)
INSERT INTO #Integers
SELECT i
FROM Integers
-- Capture a table with all the combinations.
SELECT a.i AS a, b.i AS b
INTO #Combinations
FROM #Integers a
CROSS JOIN #Integers b
-- Process each number from 2 to 10. This is more than we need but it's quick enough
DECLARE @c numeric(19,4) = 2
WHILE 1 = 1
BEGIN
-- Starting with square roots, keep repeating this process on the table until nothing can be simplified further.
WHILE 1 = 1
BEGIN
-- If the @cth root is an integer, then store the root and multiply the exponent by @c
UPDATE #Combinations
SET a = POWER(a,1.0/@c),
b = b * @c
WHERE POWER(a,1/@c) % 1 = 0
AND a > @c
AND POWER(a,1/@c) > 1
IF @@ROWCOUNT = 0 BREAK
END -- Rows to divide
-- Then repeat the process for the cube root, 4th root, etc.
SET @c = @c + 1
IF @c > 10 BREAK
END -- 2 to 10
-- And get the number of distinct combinations.
SELECT COUNT(DISTINCT CONCAT(a,'-',b))
FROM #Combinations
DROP TABLE #Integers
DROP TABLE #Combinations