Home » SQL & PL/SQL » SQL & PL/SQL » Help on desired output (oracle 10g)
Help on desired output [message #313425] Fri, 11 April 2008 23:27 Go to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
Hi All please advice

The below is the list of records in my table.

No | connect_date | act_date
167270315 8/13/2007 3/8/2007 (yes)
167270315 8/13/2007 12/13/2005 (no)
167270315 8/13/2007 8/18/2006 (no)

As you can see there are multiple entries with the same No
but all with different activation_date.

My problem is

1. how do i calculate the no of record which activation_date is the nearest to my connect_date here, for example is
connect_date is 8/13/2007 and act_date should be nearest is 3/8/2007

Urgent thank you.
Re: Help on desired output [message #313426 is a reply to message #313425] Fri, 11 April 2008 23:52 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated above.
Re: Help on desired output [message #313429 is a reply to message #313425] Sat, 12 April 2008 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Help in the desired input format: please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Use MIN function in its analytic form.

Regards
Michel
Re: Help on desired output [message #313432 is a reply to message #313429] Sat, 12 April 2008 01:23 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
Can you please assist on how to do that?
Re: Help on desired output [message #313433 is a reply to message #313432] Sat, 12 April 2008 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Use MIN function in its analytic form.

Regards
Michel
Re: Help on desired output [message #313439 is a reply to message #313433] Sat, 12 April 2008 03:27 Go to previous messageGo to next message
sunil.madnani
Messages: 36
Registered: March 2007
Location: BHILAI
Member
Use this query to generate this result.

"SELECT NO,MIN(CONNECT_DATE-ACT_DATE) FROM TEST
GROUP BY NO"


Regards:
Sunil Madnani
Re: Help on desired output [message #313442 is a reply to message #313439] Sat, 12 April 2008 03:56 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will NOT generate the ouput.
The output contains 3 columns, your query returns 2 columns.

Regards
Michel
Previous Topic: DBMS_SCHEDULER
Next Topic: External table Definitions
Goto Forum:
  


Current Time: Wed Dec 07 07:05:45 CST 2016

Total time taken to generate the page: 0.12866 seconds