Home » Other » General » Puzzle nÂ°06  Righthanded helix of Numbers ** (Oracle 9i,10g ...)
Puzzle nÂ°06  Righthanded helix of Numbers ** [message #290881] 
Wed, 02 January 2008 00:28 

rajavu1
Messages: 1574 Registered: May 2005 Location: Bangalore , India

Senior Member 


Hi all,
I am taking this Puzzle from One OP's post. Though I am not sure how much Honest the OP is .
Puzzle is to get the Righthanded Helix of Numbers like ,
1 2 3 4 5
16 17 18 19 6
15 24 25 20 7
14 23 22 21 8
13 12 11 10 9
Could any one add this to the Puzzle Sticky ?
Rajuvan.
[EDITED by LF: modified topic's title (from Z^001 to n°06) in order to follow Michel's ones]
[Updated on: Thu, 03 January 2008 05:56] by Moderator Report message to a moderator



Re: Puzzle nÂ°06  Righthanded helix of Numbers ** [message #295403 is a reply to message #290881] 
Tue, 22 January 2008 04:55 

Michel Cadot
Messages: 63912 Registered: March 2007 Location: Nanterre, France, http://...

Senior Member Account Moderator 


As for puzzle n°08, it is more an arithmetic problem than a SQL one.
Once you found the formula that gives you the value for a given (line,column), it is easy to do it in SQL.
"lines_cols" query generates &n rows for lines and columns result.
SQL> def n=3
SQL> with
2 lines_cols as ( select level n from dual connect by level <= &n )
3 select l.n line, c.n col,
4 case
5 when l.n+c.n < &n+1 and c.n >= l.n
6 then 4*(l.n1)*(&nl.n+1) + c.n  l.n + 1
7 when l.n+c.n >= &n+1 and l.n >= &n  c.n and l.n <= c.n
8 then (4*c.n2)*(&nc.n) + l.n + c.n  1
9 when l.n+c.n >= &n+1 and l.n >= c.n
10 then 4*(&nl.n)*&n  (2*(&nl.n)+1)*(2*(&nl.n)+1) + 2*&n  c.n + l.n
11 when l.n+c.n < &n+1 and c.n < l.n
12 then (3+4*(c.n1))*&n  2*c.n*(2*c.n1)  l.n  c.n + &n + 1
13 end val
14 from lines_cols l, lines_cols c
15 order by val
16 /
LINE COL VAL
  
1 1 1
1 2 2
1 3 3
2 3 4
3 3 5
3 2 6
3 1 7
2 1 8
2 2 9
Now you just have to pivot it, for instance:
SQL> set head off
SQL> with
2 lines_cols as ( select level n from dual connect by level <= &n ),
3 results as (
4 select l.n line, c.n col,
5 case
6 when l.n+c.n < &n+1 and c.n >= l.n
7 then 4*(l.n1)*(&nl.n+1) + c.n  l.n + 1
8 when l.n+c.n >= &n+1 and l.n >= &n  c.n and l.n <= c.n
9 then (4*c.n2)*(&nc.n) + l.n + c.n  1
10 when l.n+c.n >= &n+1 and l.n >= c.n
11 then 4*(&nl.n)*&n  (2*(&nl.n)+1)*(2*(&nl.n)+1) + 2*&n  c.n + l.n
12 when l.n+c.n < &n+1 and c.n < l.n
13 then (3+4*(c.n1))*&n  2*c.n*(2*c.n1)  l.n  c.n + &n + 1
14 end val
15 from lines_cols l, lines_cols c
16 )
17 select replace(substr(sys_connect_by_path(to_char(val,'9999'),'/'),2),'/',' ') res
18 from results
19 where col = &n
20 connect by prior line = line and prior col = col1
21 start with col = 1
22 order by line
23 /
1 2 3
8 9 4
7 6 5
SQL> def n=5
SQL> /
1 2 3 4 5
16 17 18 19 6
15 24 25 20 7
14 23 22 21 8
13 12 11 10 9
SQL> def n=8
SQL> /
1 2 3 4 5 6 7 8
28 29 30 31 32 33 34 9
27 48 49 50 51 52 35 10
26 47 60 61 62 53 36 11
25 46 59 64 63 54 37 12
24 45 58 57 56 55 38 13
23 44 43 42 41 40 39 14
22 21 20 19 18 17 16 15
Regards
Michel







Goto Forum:
Current Time: Sun Oct 23 19:09:34 CDT 2016
Total time taken to generate the page: 0.14266 seconds
