Re: SQL Question

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 21 Dec 2004 12:32:22 -0800
Message-ID: <113661142.00017977.094@drn.newsguy.com>

In article <1103658469.907129.255490_at_f14g2000cwb.googlegroups.com>, Bill Mill says...
>
>Hello,
>
>I would like to join two tables sequentially (I don't know the right
>term for this; I try to explain below). I am a programmer, but
>inexperienced with sql; I'd appreciate it if someone could help me out.
>
>I have a "schedule" table (which we can assume has already been sorted
>by the schedule id) with a site id number and a value like:
>
>site_id type
>101 A
>101 B
>101 B
>102 C
>102 B
>102 B
>103 A
>103 A
>
>and I want to join it with an "objects" table, which has a link to a
>site, like:
>
>obj_id site_id
>1 101
>2 102
>3 101
>4 101
>5 102
>6 103
>7 103
>
>How would I join them such that the first object with site_id of 101 is
>joined with the type of the first schedule item with site_id of 101,
>the second object of site 101 with the second schedule item from site
>101, etc. in sequential order.
>
>The result I want looks like:
>
>obj_id type site_id
>1 A 101
>2 C 102
>3 B 101
>4 B 101
>5 B 102
>6 A 103
>7 A 103
>
>Do I need to use PL/SQL to achieve this? I feel like I don't, but
>neither can I figure out how to "iterate" through the schedule table
>without producing repeats. I can think of all kinds of ugly
>programmatic ways to do it, but I feel like I'm missing an "aha" moment
>for a SQL solution.
>Any help would be appreciated.
>
>Peace
>Bill Mill
>bill.mill at gmail.com
>

wouldn't want to get carried away on the number of rows due to the amount of sorting that has to happen, but....

ops$tkyte_at_ORA9IR2> select site_id, type,   2 row_number() over (partition by site_id order by schedule_id) rn   3 from schedule
  4 order by 1, 3
  5 /  

   SITE_ID T RN
---------- - ----------

       101 A          1
       101 B          2
       101 B          3
       102 C          1
       102 B          2
       102 B          3
       103 A          1
       103 A          2
 

8 rows selected.  

ops$tkyte_at_ORA9IR2> select obj_id, site_id,   2 row_number() over(partition by site_id order by obj_id) rn   3 from objects
  4 order by 2, 3
  5 /  

    OBJ_ID SITE_ID RN
---------- ---------- ----------

         1        101          1
         3        101          2
         4        101          3
         2        102          1
         5        102          2
         6        103          1
         7        103          2
 

7 rows selected.  

ops$tkyte_at_ORA9IR2> select b.obj_id, a.type, b.site_id   2 from (
  3 select site_id, type,
  4 row_number() over (partition by site_id order by schedule_id) rn   5 from schedule

  6         ) A,
  7             (
  8  select obj_id, site_id,
  9         row_number() over(partition by site_id order by obj_id) rn
 10   from objects
 11        ) B
 12   where a.site_id = b.site_id
 13     and a.rn = b.rn

 14 order by b.obj_id
 15 /  

    OBJ_ID T SITE_ID
---------- - ----------

         1 A        101
         2 C        102
         3 B        101
         4 B        101
         5 B        102
         6 A        103
         7 A        103
 

7 rows selected.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Tue Dec 21 2004 - 14:32:22 CST