SQL Puzzle, Sort of... My group was once asked, "What's the first number that contains an 'A' in it?"

This has become a once a week exercise for fun.

I of course started counting from one to see where it might happen. Then I decided to use the magic of SQL to help me go through all the numbers.

On 10g:

SELECT num
FROM
  (
    SELECT
      TO_CHAR(TO_DATE('01/01/4712 BC', 'MM/DD/YYYY BC') + (rownum - 1), 'JSP' ) num
    FROM DUAL
      CONNECT BY LEVEL < 1000
  )
WHERE INSTR( num, 'A' ) > 0;

NUM
-------------------
ONE THOUSAND

I found the format model in the documention and moved on from there. I think my original query was a bit more elegant, but this works.


Pretty cool stuff.

Labels: database, puzzle, sql

  ¶ 8/21/2007 02:11:00 AM