T-SQL To The Rescue for Puzzle Friday!

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:

SELECT TOP 10000
        IDENTITY(INT,1,1) AS N
   INTO #Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2
GO

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,

;WITH myCTE
AS (
	SELECT N FROM #Tally
	WHERE N BETWEEN 11 AND 99 --include '11' and '99'
)
SELECT b.N as 'House Number', myCTE.N-b.N as 'Difference' FROM #Tally b
	CROSS APPLY myCTE
WHERE b.N BETWEEN 11 AND 99
	AND myCTE.N - b.N like '%2'  -- remember our difference ends in '2'
	AND myCTE.N = REVERSE(b.N)  --  the house numbers are reversed from each other
GO

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:

SELECT A.N AS 'HOUSE NUMBER', A.N-B.N AS 'DIFFERENCE'
FROM #TALLY A
	CROSS JOIN #TALLY B
WHERE
	(A.N BETWEEN 11 AND 99) AND
	(B.N BETWEEN 11 AND 99) AND
	(A.N - B.N LIKE '%2') AND
	(A.N = REVERSE(B.N))
GO

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.