WITH points AS ( SELECT LEVEL p
FROM DUAL
CONNECT BY LEVEL <= 20
UNION ALL
SELECT 25 FROM DUAL),
ab_throws
AS (SELECT p, m
FROM points
INNER JOIN
(SELECT 0 m FROM DUAL
UNION ALL
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL)
ON (m IN (0, 1)) OR (m = 2 AND p >= 11)
OR (m = 3
AND p IN
(7, 9, 11, 13, 14, 15, 16, 17, 18, 19, 20)))
SELECT n, s
FROM (SELECT DISTINCT n, s, c
FROM (SELECT n,
LTRIM(
CASE
WHEN x = 0 THEN NULL
WHEN x = 1 THEN TO_CHAR(a)
WHEN x = 2 AND a = 25 THEN 'Bull'
WHEN x = 2 THEN 'D' || TO_CHAR(a)
WHEN x = 3 THEN 'T' || TO_CHAR(a)
END
|| CASE
WHEN y = 0 THEN NULL
WHEN y = 1 THEN ' ' || TO_CHAR(b)
WHEN y = 2 AND b = 25 THEN ' Bull'
WHEN y = 2 THEN ' D' || TO_CHAR(b)
WHEN y = 3 THEN ' T' || TO_CHAR(b)
END
|| CASE
WHEN c = 25 THEN ' Bull'
ELSE ' D' || TO_CHAR(c)
END
)
s,
x,
y,
a,
b,
c,
-- Rank by number of throws, then difficulty
-- Exception: Throwing multiple Bulls is difficult due to blocking/deflection
-- Thus, a Bull is more difficult than a triple.
RANK()
OVER (
PARTITION BY n
ORDER BY
(SIGN(x) + SIGN(y) + 1),
CASE WHEN x = 2 AND a = 25 THEN 4 ELSE x END
+ CASE WHEN y = 2 AND b = 25 THEN 4 ELSE y END
)
r
FROM ( SELECT LEVEL + 3 n
FROM DUAL
CONNECT BY LEVEL < 168)
INNER JOIN (SELECT p a, m x FROM ab_throws)
ON a <= n - 4 OR x = 0
INNER JOIN (SELECT p b, m y FROM ab_throws)
ON b <= n - 4 OR y = 0
INNER JOIN (SELECT p c
FROM points
WHERE p > 1) -- avoid Double-1 as your last throw
ON 2 * c <= n
WHERE ((x * a > y * b) OR (x * a = y * b AND x = y)) -- remove unsorted duplicates
AND (x * a + y * b + 2 * c = n) -- throws must sum to the checkout
)
WHERE r = 1
UNION ALL
SELECT 2 n, 'D1' s, 2 c FROM DUAL -- 2 requires a Double-1 as the only possible checkout
UNION ALL
SELECT 3 n, '1 D1' s, 2 c FROM DUAL -- 3 requires a Double-1 as the only possible checkout
ORDER BY n, c DESC, s);
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)