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
