Home » SQL & PL/SQL » SQL & PL/SQL » needed oracle query
needed oracle query [message #269592] Sun, 23 September 2007 12:41 Go to next message
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 #269594 is a reply to message #269592] Sun, 23 September 2007 12:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow posting guidelines as stated in the #1 STICKY post as found in YELLOW/ORANGE at top of this forum.

Do NOT cross/multi post!
http://www.orafaq.com/forum/t/89818/74940/

[Updated on: Sun, 23 September 2007 12:46] by Moderator

Report message to a moderator

Re: needed oracle query [message #269598 is a reply to message #269592] Sun, 23 September 2007 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In addition, read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: needed oracle query [message #270060 is a reply to message #269598] Tue, 25 September 2007 09:51 Go to previous message
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

Previous Topic: sort given string alphabetic
Next Topic: cluster (merged)
Goto Forum:
  


Current Time: Mon Feb 17 22:14:02 CST 2025