Re: SQL Question

From: <ford_desperado_at_yahoo.com>
Date: 21 Dec 2004 13:20:13 -0800
Message-ID: <1103664013.083175.13000@c13g2000cwb.googlegroups.com>

CREATE TABLE SCHEDULE(SITE_ID NUMBER, TYPE CHAR(1));

INSERT INTO SCHEDULE VALUES(101, 'A');
INSERT INTO SCHEDULE VALUES(101, 'B');
INSERT INTO SCHEDULE VALUES(101, 'B');
INSERT INTO SCHEDULE VALUES(102, 'B');
INSERT INTO SCHEDULE VALUES(102, 'C');

CREATE TABLE OBJECTS(OBJECT_ID NUMBER, SITE_ID NUMBER);

INSERT INTO OBJECTS VALUES(1, 101);
INSERT INTO OBJECTS VALUES(3, 101);
INSERT INTO OBJECTS VALUES(4, 101);
INSERT INTO OBJECTS VALUES(2, 102);
INSERT INTO OBJECTS VALUES(5, 102);

SELECT OBJECTS_TE.OBJECT_ID, SCHEDULE_TE.SITE_ID, SCHEDULE_TE.TYPE FROM
(SELECT SITE_ID, TYPE, ROW_NUMBER() OVER(PARTITION BY SITE_ID ORDER BY
TYPE) RN FROM SCHEDULE) SCHEDULE_TE
JOIN
(SELECT OBJECT_ID, SITE_ID, ROW_NUMBER() OVER(PARTITION BY SITE_ID
ORDER BY OBJECT_ID) RN FROM OBJECTS) OBJECTS_TE ON SCHEDULE_TE.SITE_ID = OBJECTS_TE.SITE_ID AND SCHEDULE_TE.RN = OBJECTS_TE.RN; OBJECT_ID SITE_ID TYPE
---------- ---------- ----

1        101 A
3        101 B
4        101 B
2        102 B
5        102 C

5 rows selected  

DROP TABLE SCHEDULE;
DROP TABLE OBJECTS; Received on Tue Dec 21 2004 - 15:20:13 CST