T-SQL To The Rescue for Puzzle Friday!

Riddler front
Riddle me this, riddle me that!

So last Friday, I posted a puzzle given to me by a colleague.  You can see the puzzle here.   I think that I was able to solve the puzzle using T-SQL, and Jamie has since informed me that I was right!!

Here’s the puzzle again just for reference:

John lives in a house that has the reverse numbering to Jane’s house (so, for example, if John lived at number 56, Jane would be at 65).  The difference between their house number ends in 2.  Neither John or Jane live in a house numbered between 1 and 10, nor in a house number over 99. What are their house numbers?

Answer: 19 & 91.  Their difference is 72.

So How Did I Do It?

Given that I was going to work with numbers, a tally table is definitely in order. There is ton of information on the intertubes about Tally tables on how to use them. Here’s my tally table code:

One thing to note with the tally table is the number of rows selected. Since I only need 100 rows, obviously 10,000 rows gives me more than enough. You can adjust this query to fit your needs when using a tally table.

Tally ho!!! Ah, I kill me….anyway, my first thought was a common table expression, or a CTE.  Thus,


Viola!!! 19 & 91 and their difference is 72. These were the only two numbers returned.

Of course, as the day went on, I went back to my original code and was thinking that there was an easier way to do this.  So I re-wrote it to be simpler. Given that the CTE wasn’t a recursive one, you can just do a cross join on the tally table back to itself and move the WHERE clause from the anchor member down to the bottom where clause. It would look like this:

Does it get any better than this?

Viola again!!! 19 & 91 with a difference of 72!

This is one of the reasons that I love SQL Server.  There is more than one way to skin the cat!

Note:  Absoutely zero cats were harmed in writing this post.  I don’t even have a cat.

Did you find another way to solve the puzzle?  If so, let me know!

© 2011, John Morehouse. All rights reserved.

3 Replies to “T-SQL To The Rescue for Puzzle Friday!”

  1. Here is my attempt:
    declare @a int = 1;
    declare @b int = 1;

    while @a < 10
    while @b < 10
    if (abs(((10 * @a + @b) – (10 * @b + @a)))%10 = 2)
    print 10 * @a + @b
    print 10 * @b + @a
    set @b = @b + 1
    set @a = @a + 1

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.