A blog

Solving Sudoku in One SQL Query – Part 1

July 5, 2016July 5, 2016

I’ve seen a couple examples of using SQL to solve sudoku puzzles, but most of the ones I’ve seen have used loops or cursors to cycle through and create a correct answer. I feel like if you make a solution that’s procedural like that, you might as well have done it in a language that’s designed to be procedural. The real trick is to write a query that really makes use of a relational database’s parallel projection capabilities to solve a sudoku challenge

We’ll start with the fairly simple sudoku puzzle below.

The Solution

For those looking for a tl;dr, here is the complete solution for solving a sudoku puzzle with a single query. The first two query simply define and populate the given values of a puzzle, then the last query evaluates and returns a solved sudoku grid. I’ll be posting a couple more times with in depth looks at various sections of the query.

The Whole Query

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

DECLARE@givenASTABLE(RowINT,ColINT,DataINT);

INSERTINTO@given

(Row,Col,Data)

VALUES(2,1,9),(3,1,2),(4,1,5),(6,1,6),(8,1,3),

(9,1,8),(5,2,1),(6,2,3),(8,2,2),(4,3,2),

(8,3,4),(9,3,6),(2,4,3),(5,4,8),(6,4,1),

(7,4,2),(3,5,8),(5,5,9),(7,5,4),(3,6,7),

(4,6,4),(5,6,5),(8,6,6),(1,7,3),(2,7,5),

(6,7,8),(2,8,4),(4,8,1),(5,8,6),(1,9,8),

(2,9,2),(4,9,7),(6,9,4),(7,9,5),(8,9,1);

WITHCnt

AS(

SELECT1AS[val]

UNIONALL

SELECT[val]+1

FROMCnt

WHERE[val]<9

),

locations

AS(

SELECT[row].[val]AS[row]

,[col].[val]AS[col]

,(([row].[val]-1)*9)+[col].[val]AS[loc]

FROMCntAS[row]

CROSSAPPLY (

SELECTvalFROM[Cnt]

)AS[col]

),

groups

AS(

SELECT-- Groups of Rows

FLOOR(([loc]-1)/9)+101AS[grouping]

,[loc]

FROM[locations]

UNION

SELECT-- Groups of Columns

(([loc]-1)%9)+201AS[grouping]

,loc

FROM[locations]

UNION

SELECT-- Groups of 3x3 Squares

FLOOR(([row]-1)/3)*3+FLOOR(([col]-1)/3)+301[grouping]

,[locations].[loc]

FROMlocations

),

Possibilities

AS(

SELECT[g].[Row]

,[g].[Col]

,[locations].[loc]

,[g].[Data]

FROM@givenAS[g]

JOIN[locations]

ON[locations].[col]=[g].[Col]

AND[locations].[row]=[g].[Row]

UNIONALL

SELECT[possibility].[row]

,[possibility].[col]

,[possibility].[loc]

,[possibility].[val]

FROM(

SELECT[locations].*

,[possibleValue].[val]

FROM[locations]

LEFTOUTERJOIN@givenAS[g]

ON[g].[Col]=[locations].[col]

AND[g].[Row]=[locations].[row]

CROSSAPPLY (

SELECT*FROM[Cnt]

)AS[possibleValue]

WHERE[g].[Data]ISNULL--Exclude any cells that are already defined in the given set