Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Question

Re: SQL Question

From: Anil G <anil.gothal_at_gmail.com>
Date: 12 Sep 2006 21:14:34 -0700
Message-ID: <1158120874.546722.95730@d34g2000cwd.googlegroups.com>


Guys:

I am trying to work with Analytic functions available in Oracle,

SELECT org,home,MAX(t_qty) OVER (PARTITION BY org,home) FROM TBL01
WHERE home IS NOT NULL

Still struggling..

Thanks,

Galen Boyer wrote:
> Hope you get your homework answered.
>
>
> On 12 Sep 2006, anil.gothal_at_gmail.com wrote:
> > 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;
>
> --
> Galen Boyer
Received on Tue Sep 12 2006 - 23:14:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US