Re: An SQL Challenge...

From: AK <AK_TIREDOFSPAM_at_hotmail.COM>
Date: 21 Jun 2005 10:32:01 -0700
Message-ID: <1119375121.476769.40690@z14g2000cwz.googlegroups.com>

analytics rock!

I used a small test table for my unit test:

create table test_data1
as
select id, degrees from test_data where rownum<15

select * from test_data1

        ID DEGREES
---------- ----------

         0        235
         0        276
         1        211
         1        250
         1        255
         2         55
         2        277
         2         69
         3         25
         3        166
         3        262
         4         47
         4        238
         4         40

14 rows selected

• choose the starting cell

select id, degrees from(
select id, degrees, (360 - degrees) d360, row_number() over(partition by id order by case
  when (degrees < 360 - degrees) then degrees   else 360 - degrees
end) rn
from test_data1) t
where rn = 1

        ID DEGREES
---------- ----------

         0        276
         1        255
         2         55
         3         25
         4         40

5 rows selected

• order the rest clockwise select * from( select t.id, t.degrees, row_number() over(partition by t.id order by case when (t.degrees < starting_cell.degrees) then t.degrees + 360 else t.degrees end) rn from test_data1 t join ( select id, degrees, (360 - degrees) d360, row_number() over(partition by id order by case when (degrees < 360 - degrees) then degrees else 360 - degrees end) rn from test_data1) starting_cell on t.id = starting_cell.id where starting_cell.rn=1 )t order by id, rn ID DEGREES RN
  ---------- ---------- ----------
  0 276 1 0 235 2 1 255 1 1 211 2 1 250 3 2 55 1 2 69 2 2 277 3 3 25 1 3 166 2 3 262 3 4 40 1 4 47 2 4 238 3 14 rows selected

HIH
Alexander Received on Tue Jun 21 2005 - 12:32:01 CDT