-- File: reindeer.sql -- Version: 1.1 -- Last Changed: 2017-12-02 -- by: Damir; https://www.damirsystems.com -- Project: Reindeer Ordering -- Description: Fun challenge from Decision Management Community -- https://dmcommunity.org/challenge/challenge-dec-2017/ -- DB: PostgreSQL, MS SQL Server WITH ord AS ( -- single column of integers (1..9) SELECT p FROM ( values (1), (2), (3), (4), (5), (6), (7), (8), (9) ) AS x(p) ), possible AS ( -- generate 9! = 362880 possible permutations SELECT a.p AS Blitzen , b.p AS Comet , c.p AS Cupid , d.p AS Dancer , e.p AS Dasher , f.p AS Donder , g.p AS Prancer , h.p AS Rudolph , k.p AS Vixen FROM ord AS a JOIN ord AS b ON b.p != a.p JOIN ord AS c ON c.p NOT IN (a.p, b.p) JOIN ord AS d ON d.p NOT IN (a.p, b.p, c.p) JOIN ord AS e ON e.p NOT IN (a.p, b.p, c.p, d.p) JOIN ord AS f ON f.p NOT IN (a.p, b.p, c.p, d.p, e.p) JOIN ord AS g ON g.p NOT IN (a.p, b.p, c.p, d.p, e.p, f.p) JOIN ord AS h ON h.p NOT IN (a.p, b.p, c.p, d.p, e.p, f.p, g.p) JOIN ord AS k ON k.p NOT IN (a.p, b.p, c.p, d.p, e.p, f.p, g.p, h.p) ) -- apply rules to the set of all possible permutations -- and keep results that satisfy the rule-set SELECT Blitzen , Comet , Cupid , Dancer , Dasher , Donder , Prancer , Rudolph , Vixen FROM possible WHERE (1=1) -- 1. Comet behind Rudolph, Prancer and Cupid AND (Comet > Rudolph AND Comet > Prancer AND Comet > Cupid) -- 2. Blitzen behind Cupid AND (Blitzen > Cupid) -- 3. Blitzen in front of Donder, Vixen and Dancer AND (Blitzen < Donder AND Blitzen < Vixen AND Blitzen < Dancer) -- 4. Cupid in front of Comet, Blitzen and Vixen AND (Cupid < Comet AND Cupid < Blitzen AND Cupid < Vixen) -- 5. Donder behind Vixen, Dasher and Prancer AND (Donder > Vixen AND Donder > Dasher AND Donder > Prancer) -- 6. Rudolph behind Prancer AND (Rudolph > Prancer) -- 7. Rudolph in front of Donder, Dancer and Dasher AND (Rudolph < Donder AND Rudolph < Dancer AND Rudolph < Dasher) -- 8. Vixen in front of Dancer and Comet AND (Vixen < Dancer AND Vixen < Comet) -- 9. Dancer behind Donder, Rudolph and Blitzen AND (Dancer > Donder AND Dancer > Rudolph AND Dancer > Blitzen) -- 10. Prancer in front of Cupid, Donder and Blitzen AND (Prancer < Cupid AND Prancer < Donder AND Prancer < Blitzen) -- 11. Dasher behind Prancer AND (Dasher > Prancer) -- 12. Dasher in front of Vixen, Dancer and Blitzen AND (Dasher < Vixen AND Dasher < Dancer AND Dasher < Blitzen) -- 13. Donder behind Comet and Cupid AND (Donder > Comet AND Donder > Cupid) -- 14. Cupid in front of Rudolph and Dancer AND (Cupid < Rudolph AND Cupid < Dancer) -- 15. Vixen behind Rudolph, Prancer and Dasher AND (Vixen > Rudolph AND Vixen > Prancer AND Vixen > Dasher) ;