Home » Other » General » Puzzle n°06 - Right-handed helix of Numbers ** (Oracle 9i,10g ...)
Puzzle n°06 - Right-handed helix of Numbers ** [message #290881] Wed, 02 January 2008 00:28 Go to next message
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 Right-handed 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 


1   2   3
8   9   4
7   6   5


Could any one add this to the Puzzle Sticky ?

Thumbs Up
Rajuvan.

[EDITED by LF: modified topic's title (from Z^001 to n06) 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 - Right-handed helix of Numbers ** [message #295403 is a reply to message #290881] Tue, 22 January 2008 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As for puzzle n08, 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.n-1)*(&n-l.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.n-2)*(&n-c.n) + l.n + c.n - 1
  9               when l.n+c.n >= &n+1 and l.n >= c.n 
 10                 then 4*(&n-l.n)*&n - (2*(&n-l.n)+1)*(2*(&n-l.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.n-1))*&n - 2*c.n*(2*c.n-1) - 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.n-1)*(&n-l.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.n-2)*(&n-c.n) + l.n + c.n - 1
 10               when l.n+c.n >= &n+1 and l.n >= c.n 
 11                 then 4*(&n-l.n)*&n - (2*(&n-l.n)+1)*(2*(&n-l.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.n-1))*&n - 2*c.n*(2*c.n-1) - 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 = col-1
 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
Re: Puzzle n°06 - Right-handed helix of Numbers ** [message #295425 is a reply to message #290881] Tue, 22 January 2008 05:30 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Michel , You are simply rocking again (though i need to study the code ).

Actually I Posted Puzzle 08 for getting a starting kick for this Puzzle 06 Only . But Michel Overtook me all the way .

Rolling Eyes Rolling Eyes Rolling Eyes

Thumbs Up
Rajuvan.
Re: Puzzle n°06 - Right-handed helix of Numbers ** [message #295488 is a reply to message #295425] Tue, 22 January 2008 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you (or anyone) find a simpler formula giving the value from line and column numbers this will be very welcome.
Currently, the case branches give the values from, respectively:
- top lines from left to right
- right columns from top to bottom
- bottom lines from right to left
- left columns from bottom to top
following the first square and then reducing at each lap, so the complex conditions.

Puzzle 08 was much more simpler as values increase with line and column numbers.

Regards
Michel
Re: Puzzle n°06 - Right-handed helix of Numbers ** [message #295495 is a reply to message #290881] Tue, 22 January 2008 08:23 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Puzzle 08 was much more simpler as values increase with line and column numbers.


But Both the puzzles have 2 stars (**) Smile

Thumbs Up
Rajuvan
Re: Puzzle n°06 - Right-handed helix of Numbers ** [message #295503 is a reply to message #295495] Tue, 22 January 2008 08:38 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a rating for SQL (or PL/SQL) difficulty not rating for arithmetical one.

Regards
Michel
Previous Topic: LEAD and LAG function
Next Topic: DBA
Goto Forum:
  


Current Time: Sat Dec 10 05:08:01 CST 2016

Total time taken to generate the page: 0.16161 seconds