Needed oracle query

[laxmikumar999]

Hi all,

I have a data as given below.

ID RATE1 DATE1 RATE2 DATE2 CODE
10 A 2007-FEB-01 U 2007-JAN-01 BIG
10 B 2007-FEB-01 V 2007-JAN-01 BIG
10 C 2007-JAN-01 W 2007-FEB-01 SMALL
10 D 2007-FEB-01 X 2007-JAN-01 SMALL


I have to write a query for which I will describe my requirement step by step as given below

We will first aim to fetch rows based on ID,RATE1,DATE1

STEP1:
I need to fetch rows based on ID,RATE1,DATE1 with the rows having latest DATE1 for same ID(here 100).So my output will be as follows

ID RATE1 DATE1 CODE
10 A 2007-FEB-01 BIG
10 B 2007-FEB-01 BIG
10 D 2007-FEB-01 SMALL

STEP2:-
Now from the output of as got in step1,we need to fetch rows based on CODE (i.e) if the CODE is BIG and SMALL for selected rows then we need select only rows having BIG only else select rows having CODE=SMALL.So my output will be as follows

ID RATE1 DATE1 CODE
10 A 2007-FEB-01 BIG
10 B 2007-FEB-01 BIG

So output from step2 will be for ID,RATE1,DATE1.

Now we need to fetch rows based on ID,RATE2,DATE2

STEP3:
I need to fetch rows based on ID,RATE2,DATE2 with the rows having latest DATE2 for same ID(here 100).So my output will be as follows

ID RATE1 DATE1 CODE
10 W 2007-FEB-01 SMALL




STEP4:-
Now from the output of as got in step1,we need to fetch rows based on CODE (i.e) if the CODE is BIG and SMALL for selected rows then we need select only rows having BIG only else select rows having CODE=SMALL.So my output will be as follows

ID RATE1 DATE1 CODE
10 W 2007-FEB-01 SMALL

So output from step4 will be for ID,RATE2,DATE2.

On combining the output from step2 and step4 I need the final output as given below

ID RATE1 DATE1 RATE2 DATE2 CODE
10 A 2007-FEB-01 W 2007-FEB-01 BIG
10 B 2007-FEB-01 W 2007-FEB-01 BIG
10 B 2007-FEB-01 W 2007-FEB-01 SMALL


Could anyone please provide an oracle query for the given requirement.

Thanks in advance,

Regards,
Laxmi

[amitpatel66]

Hi all,

I have a data as given below.

ID RATE1 DATE1 RATE2 DATE2 CODE
10 A 2007-FEB-01 U 2007-JAN-01 BIG
10 B 2007-FEB-01 V 2007-JAN-01 BIG
10 C 2007-JAN-01 W 2007-FEB-01 SMALL
10 D 2007-FEB-01 X 2007-JAN-01 SMALL

Thanks in advance,

Regards,
Laxmi

In step 2, you are saying you want the records with code = BIG else code = SMALL then how come only two records will be in the output of step2??

All the three records should be in the output of step2.