needed oracle query [message #269592] |
Sun, 23 September 2007 12:41  |
laxmikumar999
Messages: 1 Registered: September 2007
|
Junior Member |
|
|
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
|
|
|
|
|
Re: needed oracle query [message #270060 is a reply to message #269598] |
Tue, 25 September 2007 09:51  |
djayatosu
Messages: 6 Registered: September 2007 Location: USA
|
Junior Member |
|
|
Laxmi,
I can't really comprehend your step1 itself.
ID RATE1 DATE1 RATE2 DATE2 CODE
10 A 2/1/2007 U 1/1/2007 BIG
10 B 2/1/2007 V 1/1/2007 BIG
10 C 1/1/2007 W 2/1/2007 SMALL
10 D 2/1/2007 X 1/1/2007 SMALL
Your step 1:
Quote: | 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....
|
The 'ID' is the same for all the rows. Rate1 is different for all the records? So, you have a unique set of records. On what basis did you get this result from step1??
You need to explain your requirement more clearly.
Regards,
Jay
|
|
|