Post date: Jun 19, 2014 12:49:53 AM
Still working from Google Cache. "Take one each of the the digits 0 through 9, order them 'alphabetically,' and find the one millionth result."
This is FAR uglier than I like. But the speed in undeniable. Just calculate 2 digit combos, then add one digit, then one more, etc, always making sure that no digits are duplicated. I could write it as a straight inner join, but that adds over 150% more processing time.
CREATE TABLE #Integers (i INT PRIMARY KEY NOT NULL)
INSERT INTO #Integers VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
; WITH Top1Million AS
(
SELECT TOP (1000000) b.i0, b.i1, b.i2, b.i3, b.i4, b.i5, b.i6, b.i7, b.i8, a.i AS i9
FROM #Integers a
INNER JOIN
(
SELECT b.i0, b.i1, b.i2, b.i3, b.i4, b.i5, b.i6, b.i7, a.i AS i8
FROM #Integers a
INNER JOIN
(
SELECT b.i0, b.i1, b.i2, b.i3, b.i4, b.i5, b.i6, a.i AS i7
FROM #Integers a
INNER JOIN
(
SELECT b.i0, b.i1, b.i2, b.i3, b.i4, b.i5, a.i AS i6
FROM #Integers a
INNER JOIN
(
SELECT b.i0, b.i1, b.i2, b.i3, b.i4, a.i AS i5
FROM #Integers a
INNER JOIN
(
SELECT b.i0, b.i1, b.i2, b.i3, a.i AS i4
FROM #Integers a
INNER JOIN
(
SELECT b.i0, b.i1, b.i2, a.i AS i3
FROM #Integers a
INNER JOIN
(
SELECT b.i0, b.i1, a.i AS i2
FROM #Integers a
INNER JOIN
(
SELECT a.i AS i0, b.i AS i1
FROM #Integers a
INNER JOIN #Integers b ON a.i <> b.i
) AS b
ON b.i0 <> a.i
AND b.i1 <> a.i
) AS b
ON b.i0 <> a.i
AND b.i1 <> a.i
AND b.i2 <> a.i
) AS b
ON b.i0 <> a.i
AND b.i1 <> a.i
AND b.i2 <> a.i
AND b.i3 <> a.i
) AS b
ON b.i0 <> a.i
AND b.i1 <> a.i
AND b.i2 <> a.i
AND b.i3 <> a.i
AND b.i4 <> a.i
) AS b
ON b.i0 <> a.i
AND b.i1 <> a.i
AND b.i2 <> a.i
AND b.i3 <> a.i
AND b.i4 <> a.i
AND b.i5 <> a.i
) AS b
ON b.i0 <> a.i
AND b.i1 <> a.i
AND b.i2 <> a.i
AND b.i3 <> a.i
AND b.i4 <> a.i
AND b.i5 <> a.i
AND b.i6 <> a.i
) AS b
ON b.i0 <> a.i
AND b.i1 <> a.i
AND b.i2 <> a.i
AND b.i3 <> a.i
AND b.i4 <> a.i
AND b.i5 <> a.i
AND b.i6 <> a.i
AND b.i7 <> a.i
) AS b
ON b.i0 <> a.i
AND b.i1 <> a.i
AND b.i2 <> a.i
AND b.i3 <> a.i
AND b.i4 <> a.i
AND b.i5 <> a.i
AND b.i6 <> a.i
AND b.i7 <> a.i
AND b.i8 <> a.i
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
)
SELECT MAX(CONVERT(char(1),i0) +
CONVERT(char(1),i1) +
CONVERT(char(1),i2) +
CONVERT(char(1),i3) +
CONVERT(char(1),i4) +
CONVERT(char(1),i5) +
CONVERT(char(1),i6) +
CONVERT(char(1),i7) +
CONVERT(char(1),i8) +
CONVERT(char(1),i9))
FROM Top1Million
DROP TABLE #Integers