{"id":450,"date":"2008-10-09T00:00:00","date_gmt":"2008-10-09T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-bejeweled-puzzle-in-sql\/"},"modified":"2021-09-29T16:22:09","modified_gmt":"2021-09-29T16:22:09","slug":"the-bejeweled-puzzle-in-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-bejeweled-puzzle-in-sql\/","title":{"rendered":"The Bejeweled Puzzle in SQL"},"content":{"rendered":"
\n

Many DBAs and developers, at least those that have their servers and applications well under control, may be familiar with the popular, and rather addictive, PC and Xbox games such as Bejeweled and Jewel Quest. They belong to the group of ‘match 3’ puzzles that are played on grids of different sizes, though typically 8×8. The grid cells are filled with tokens, such as gems or coins, of various shapes and colours and the basic idea is to create horizontal or vertical rows containing a minimum of three matching tokens, by swapping adjacent tokens in the grid. For example, in the ‘bejewelled’ puzzle shown in Figure 1, we can create a sequence of three blue stones in the second row of the grid by swapping the highlighted red stone with the blue one directly below it:<\/p>\n

\"580-image001.jpg\"
Figure 1. <\/b>Bejeweled puzzle<\/p>\n

All swaps must be done vertically or horizontally and only swaps that create a sequence of at least three tokens of the same kind, in a column or row, are permitted. When a successful swap is made, the sequence of collected tokens disappears, creating holes (gaps) in the grid. The tokens above these holes immediately cascade down to fill these holes, in turn creating holes in the  top cells in the corresponding columns, which are filled by randomly-generated new tokens. The more tokens a player can make disappear in a single swap, the more points the player gets and the faster he can advance to the next level.<\/p>\n

After each swap, the game engine assesses the situation on the game board and if no further valid moves can be made, the game is over. In timed variants, the game can be also terminated if time expires. If valid moves exist, but a player does not make it during a certain time, or if a player presses the “Hint” button, the game engine shows the move that can be made.<\/p>\n

The SQL Puzzle<\/h2>\n

It occurred to me that it would be a very interesting challenge to recreate a bejewled-style puzzle and solve it using SQL!<\/p>\n

Figure 2 shows a table, called bejewelled<\/b>, which represents 9×9 Bejeweled grid:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

#<\/b><\/p>\n<\/td>\n

\n

1<\/b><\/p>\n<\/td>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

3<\/b><\/p>\n<\/td>\n

\n

4<\/b><\/p>\n<\/td>\n

\n

5<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

7<\/b><\/p>\n<\/td>\n

\n

8<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/b><\/p>\n<\/td>\n

\n

8<\/b><\/p>\n<\/td>\n

\n

5<\/b><\/p>\n<\/td>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

8<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

8<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

1<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

3<\/b><\/p>\n<\/td>\n

\n

8<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

4<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

3<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

3<\/b><\/p>\n<\/td>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

8<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

3<\/b><\/p>\n<\/td>\n

\n

4<\/b><\/p>\n<\/td>\n

\n

3<\/b><\/p>\n<\/td>\n

\n

7<\/b><\/p>\n<\/td>\n

\n

4<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

4<\/b><\/p>\n<\/td>\n

\n

1<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

4<\/b><\/p>\n<\/td>\n

\n

5<\/b><\/p>\n<\/td>\n

\n

7<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

5<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

5<\/b><\/p>\n<\/td>\n

\n

7<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

7<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

8<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

5<\/b><\/p>\n<\/td>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

5<\/b><\/p>\n<\/td>\n

\n

8<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

8<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

7<\/b><\/p>\n<\/td>\n

\n

4<\/b><\/p>\n<\/td>\n

\n

4<\/b><\/p>\n<\/td>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

5<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

5<\/b><\/p>\n<\/td>\n

\n

8<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

8<\/b><\/p>\n<\/td>\n

\n

5<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

3<\/b><\/p>\n<\/td>\n

\n

7<\/b><\/p>\n<\/td>\n

\n

7<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

3<\/b><\/p>\n<\/td>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

7<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

6<\/b><\/p>\n<\/td>\n

\n

2<\/b><\/p>\n<\/td>\n

\n

9<\/b><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Figure 2.<\/b> Table bejeweled<\/p>\n

The blue numbers from 1 to 9 symbolize nine different types of tokens. In this case, we are dealing only with a fixed grid, rather than one that continually adds randomly-generated tokens, but the goal is similar: you must swap adjacent tokens to create an island of three or more identical numbers in a row or column. For example, if you swap the values of columns 1 and 2 in row 1, you will create a sequence of three 8’s in column 2 (rows 1, 2 and 3).<\/p>\n

The Challenge!<\/h2>\n

So, here was the challenge! Using SQL or Transact-SQL, you needed to find all of the possible moves <\/b>(horizontal and vertical) for the snapshot of Bejeweled table shown in Figure 2.<\/p>\n

You had to  present your solution in the following format:<\/p>\n\n\n\n\n\n\n
\n

Row number<\/b><\/p>\n<\/td>\n

\n

Column number<\/b><\/p>\n<\/td>\n

\n

Value<\/b><\/p>\n<\/td>\n<\/tr>\n

\n

1<\/p>\n<\/td>\n

\n

1<\/p>\n<\/td>\n

\n

8<\/p>\n<\/td>\n<\/tr>\n

\n

5<\/p>\n<\/td>\n

\n

4<\/p>\n<\/td>\n

\n

9<\/p>\n<\/td>\n<\/tr>\n

\n

Etc…<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n

\n

…<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

You did not need to show where<\/b> to move the value of the specific cell. Just show what<\/b> value to move, and from what<\/b> cell it should be moved.<\/p>\n

There were no constraints on how you coded the solution: you could use any SQL and Transact-SQL statements, commands and build-in functions. SQL Server version-specific solutions (for instance, for SQL Server 2008 only) were also allowed. However you did it, you had to present your SQL code, along with a brief explanation of algorithm \/ implementation you used.<\/p>\n

The script in Listing 1  allowed you to create and load table bejeweled:<\/p>\n

Listing1.<\/b> Create and load table bejewelled<\/p>\n

IF EXISTS(SELECT * FROM sysobjects \n   WHERE ID = (OBJECT_ID('bejeweled')) AND xtype = 'U')\nDROP TABLE bejeweled\nGO\n\nCREATE TABLE bejeweled(\n [#] [tinyint] NULL,\n [1] [tinyint] NULL,\n [2] [tinyint] NULL,\n [3] [tinyint] NULL,\n [4] [tinyint] NULL,\n [5] [tinyint] NULL,\n [6] [tinyint] NULL,\n [7] [tinyint] NULL,\n [8] [tinyint] NULL,\n [9] [tinyint] NULL\n)\n\nINSERT INTO bejeweled VALUES(1, 8, 5, 2, 8, 6, 8, 9, 9, 1);\nINSERT INTO bejeweled VALUES(2, 3, 8, 6, 4, 9, 3, 6, 9, 6);\nINSERT INTO bejeweled VALUES(3, 2 , 8, 9, 3, 4, 3, 7, 4, 9);\nINSERT INTO bejeweled VALUES(4, 1, 9, 4, 5, 7, 9, 9, 5, 6);\nINSERT INTO bejeweled VALUES(5, 7, 6, 2, 9, 7, 9, 9, 6, 8);\nINSERT INTO bejeweled VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8, 9);\nINSERT INTO bejeweled VALUES(7, 4, 4, 2, 5, 9, 5, 8, 6, 9);\nINSERT INTO bejeweled VALUES(8, 5, 9, 9, 3, 7, 7, 6, 2, 6);\nINSERT INTO bejeweled VALUES(9, 2, 3, 2, 7, 9, 6, 6, 2, 9);\n<\/pre>\n
There were three winners:<\/p>\n
    \n
  • Timothy Walters<\/b> – for his second solution with the offset table; He gets a $50 Amazon Voucher.<\/i>  <\/li>\n
  • Ryan Randall<\/b> – for his first and second solutions; He gets a choice between a license for SQL Prompt and SQL Data Generator<\/i>  <\/li>\n
  • DrLechter<\/b> – for his first and second solutions; He gets a choice between a license for SQL Prompt and SQL Data Generator<\/i> <\/li>\n<\/ul>\n
    Timothy Walters’s solution is very simple and elegant. Almost all of the logic has been implemented in the lookup table and that makes the solution portable and flexible.<\/p>\n
    In addition, his solution can be easiely modified in order to show the directions of the moves by adding one more column to the offset table. <\/p>\n
    Here is how it can be done: <\/p>\n

    --====== Table matches needs to be loaded only once
    CREATE TABLE matches(offsetRow1 INT, offsetCol1 INT, offsetRow2 INT, ofsetCol2 INT, directions VARCHAR(20))
    -- for horizontal 
    INSERT INTO matches VALUES(-1, -1, -1, -2, 'up')
    INSERT INTO matches VALUES(-1, -1, -1, 1, 'up')
    INSERT INTO matches VALUES(-1, 1, -1, 2, 'up')        
    INSERT INTO matches VALUES( 1, -1, 1, -2, 'down')           
    INSERT INTO matches VALUES( 1, -1, 1, 1, 'down')
    INSERT INTO matches VALUES( 1, 1, 1, 2, 'down')       
    INSERT INTO matches VALUES( 0, -2, 0, -3, 'left')     
    INSERT INTO matches VALUES( 0, 2, 0, 3, 'right')            
    -- for verical
    INSERT INTO matches VALUES(-2, -1, -1, -1, 'left')
    INSERT INTO matches VALUES(-1, -1, 1, -1, 'left')
    INSERT INTO matches VALUES( 1, -1, 2, -1, 'left')
    INSERT INTO matches VALUES(-2, 1, -1, 1, 'right')
    INSERT INTO matches VALUES(-1, 1, 1, 1, 'right')
    INSERT INTO matches VALUES( 1, 1, 2, 1, 'right')
    INSERT INTO matches VALUES(-2, 0, -3, 0, 'up')
    INSERT INTO matches VALUES( 2, 0, 3, 0, 'down')<\/p>\n
    --==================================================
    ;WITH CTE
          AS
      (
      SELECT
            [Row] = CAST( [#] AS INT ),
            [Col] = CAST( [Col] AS INT ),
            [Value]
        FROM bejeweled
            UNPIVOT ([Value] FOR [Col] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) unpvt
      )
    SELECT DISTINCT T.Row, T.Col, T.Value, directions
      FROM CTE T
          JOIN CTE T1
          ON T.Value = T1.Value
          JOIN CTE T2
          ON T.Value = T2.Value
          JOIN matches
          ON (T1.Row - T.Row) = offsetRow1
        AND (T1.Col - T.Col) = offsetCol1
        AND (T2.Row - T.Row) = offsetRow2
        AND (T2.Col - T.Col) = ofsetCol2
      ORDER BY T.Row, T.Col
    <\/code> <\/p>\n
    There is one more person – AmandaS that should be specially mentioned. Her second solution was very similar to Timothy Walters’ and Ryan Randall’s solutions, but was submitted five days later.<\/p>\n
    A few more people should be mentioned as well:<\/p>\n
      \n
    • Saharafrog <\/b>– interesting solution  <\/li>\n
    • Doug Stewart <\/b> <\/li>\n
    • Steve Munson<\/b> – his solution shows the coordinates of the cells that should be moved and the coordinates of destination cells  <\/li>\n
    • RBarry Young <\/b> <\/li>\n
    • Dennis A<\/b>  <\/li>\n
    • SkyBeaver <\/b><\/li>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
      Alex Kozak provides another SQL puzzle to hone your SQL Skills with.  …<\/p>\n","protected":false},"author":221828,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4252],"coauthors":[],"class_list":["post-450","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/450","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221828"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=450"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/450\/revisions"}],"predecessor-version":[{"id":92560,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/450\/revisions\/92560"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=450"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=450"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=450"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=450"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}