SQL Question

Please create following table into schema, I am trying to develop SQL query to retrieve following results

ORG HOME T_QTY E_QTY
---- ---- ----- -----
O14 O1 15 109
O23 O2 27 200

Following the logic involved:

1. Org and home has parent-child relationship
2. Find Org having max of t_qty and then by e_qty for each HOME. 2.1. If two records have same t_qty then choose the one with max of e_qty
3. Sql performed per HOME; infact where should have home as NOT NULL;

CREATE TABLE TBL01
(

  ORG    VARCHAR2(10 BYTE)                          NULL,
  HOME   VARCHAR2(10 BYTE)                          NULL,
  T_QTY  INTEGER                                    NULL,
  E_QTY  INTEGER                                    NULL

CREATE UNIQUE INDEX TBL01_PK ON TBL01
(ORG)
LOGGING
NOPARALLEL; ALTER TABLE TBL01 ADD (
  CONSTRAINT TBL01_PK
 PRIMARY KEY
 (ORG)); ALTER TABLE TBL01 ADD (
  CONSTRAINT TBL01_HOME_FK
 FOREIGN KEY (HOME)
 REFERENCES TBL01 (ORG)); INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O1', NULL, 100, 3000);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O10', 'O1', 10, 100);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O11', 'O1', 11, 90);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O12', 'O1', 12, 101);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O13', 'O1', 12, 103);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O14', 'O1', 15, 109);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O15', 'O1', 12, 109);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O2', NULL, 132, 1009);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O20', 'O2', 11, 89);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O21', 'O2', 12, 109);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O22', 'O2', 24, 209);

INSERT INTO TBL01 ( ORG, HOME, T_QTY, E_QTY ) VALUES (
'O23', 'O2', 27, 200);

COMMIT; Received on Tue Sep 12 2006 - 22:47:04 CDT