An SQL Challenge...

From: <mike.jones_at_xenicom.com>
Date: 21 Jun 2005 07:37:46 -0700
Message-ID: <1119364666.315642.141220@g44g2000cwa.googlegroups.com>

I was recently given a datacleanse task that I think makes an interesting SQL Puzzle. At the end are the setup statement you will need.

I guess replies to this thread, so far I've been unable to do it purely in SQL I have had to drop (Briefly) into PL/SQL, but I'm sure there must be an SQL only solution.

If it helps motivate people I've also challenged some C++ and Java developers to see if they can beat SQL / PL/SQL, come on now we can't let the side down!

Mike.

---

The challenge is to sequence the cells on a Site. The Sequence should start with the Cell that is closest to 0 degrees i.e. 10 is closer than 20, but 358 is closer than 10. Once the first Cell is sequenced the remaining cells should be sequenced clockwise (think of the degrees ploted on a circle) around the remaining records. E.g.

Degree Seq No

358	1
10	2
180	3
181	4

359	1
354	2
355	3

0	1
180	2
350	3

Where two records are equal distance say the set is 10, 180, 350) then either 10 or 350 can be considered "closest", but the sequencing rule above must still be used (Clockwise around the circle from that point)

The below will create a table called TEST_DATA, This has 3 main columns ID, DEGREES and SEQUENCE_NO. For each ID there will be 3 records (But don't assume this - the solution should work with any number), with DEGREES between 0 and 359.

drop table test_data
/

create table test_data unrecoverable tablespace data_large as
select trunc(rownum / 3)id, lpad('*',500,'*') filler_1,
lpad('@',500,'@') filler_2, lpad('#',500,'#') filler_3,
trunc(dbms_random.value(0,360)) degrees, -1 sequence_no

from
( select *
  from dual
  connect by level = level
)
where rownum <= 200000
/

create index test_data_idx1 on test_data (id) unrecoverable
/

begin
  loop
    --
    update test_data
    set degrees = degrees - decode(degrees,0, -1,1)     where rowid in
    ( select min(rowid) min_row_id

      from test_data
      group by id, degrees
      having count(*) > 1

    );
    --
    exit when sql%rowcount = 0;
  end loop;
end;
/
Received on Tue Jun 21 2005 - 09:37:46 CDT