Home » SQL & PL/SQL » SQL & PL/SQL » Greatest Date
Greatest Date [message #416462] Mon, 03 August 2009 00:37 Go to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Dear All,

Please help how can find out the greatest date among the column with null.
INPUT
Name Last_A_Usged Last_B_Used   May          Jun
Abc  5/10/09      5/11/09       5/10/08     5/08/09
bbc  5/10/09      5/11/09                   5/08/09
cbc  5/10/09      5/11/09       5/12/09     

OUTPUT
Name Last_Used
Abc  5/11/09      
bbc  5/11/09     
cbc  5/12/09

Please help


Regards,
Arvind kumar

[EDITED by LF: applied [pre] tags]

[Updated on: Mon, 03 August 2009 00:43] by Moderator

Report message to a moderator

Re: Greatest Date [message #416465 is a reply to message #416462] Mon, 03 August 2009 00:42 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Combine GREATEST with NVL.

[EDIT] Forgot to ask ... what does "5/10/09" represent? Is it
5th of October 2009
5th of September 2010
10th of May 2009
9th of May 2010
...

[Updated on: Mon, 03 August 2009 00:48]

Report message to a moderator

Re: Greatest Date [message #416477 is a reply to message #416465] Mon, 03 August 2009 01:21 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Littlefoot wrote on Mon, 03 August 2009 11:12
Combine GREATEST with NVL.

[EDIT] Forgot to ask ... what does "5/10/09" represent? Is it
5th of October 2009
5th of September 2010
10th of May 2009
9th of May 2010
...



Thanks but i have tried through this bit not able to find out exact out put can u provide the query so that it's helpfull for us.

Arvind
Re: Greatest Date [message #416480 is a reply to message #416477] Mon, 03 August 2009 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68727
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Show us what you tried.
Before 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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Greatest Date [message #416483 is a reply to message #416462] Mon, 03 August 2009 01:44 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
yes date respresent as per you but need last use please send me query Please help us ....
Re: Greatest Date [message #416497 is a reply to message #416480] Mon, 03 August 2009 02:08 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
I am using Oracle 10g - Release 10.2.0.3.0 - 64bi

Re: Greatest Date [message #416498 is a reply to message #416483] Mon, 03 August 2009 02:15 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
kr_arvind wrote on Mon, 03 August 2009 08:44
Please help us ....

/forum/fa/450/0/ Gollum, is that you?!?

kr_arvind
yes date respresent as per you
Which one, exactly?
Re: Greatest Date [message #416500 is a reply to message #416498] Mon, 03 August 2009 02:21 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Date stored in database as per below format.

Name Last_A_Usged Last_B_Used May Jun
Abc 5/10/09 5/11/09 5/10/08 5/08/09
bbc 5/10/09 5/11/09 5/08/09
cbc 5/10/09 5/11/09 5/12/09
Re: Greatest Date [message #416501 is a reply to message #416500] Mon, 03 August 2009 02:23 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
But what does that format mean? If you don't know what the format means, do to the person who designed the database and ask them.
Re: Greatest Date [message #416503 is a reply to message #416501] Mon, 03 August 2009 02:31 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Date format is dd/mm/yyyy

Re: Greatest Date [message #416504 is a reply to message #416503] Mon, 03 August 2009 02:34 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
kr_arvind wrote on Mon, 03 August 2009 09:31
Date format is dd/mm/yyyy



Quote:
5/08/09


No, it isn't. If the date format is dd/mm/yyyy, then that date would be the fifth of August in the year nine.

[Updated on: Mon, 03 August 2009 02:35]

Report message to a moderator

Re: Greatest Date [message #416505 is a reply to message #416504] Mon, 03 August 2009 02:40 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And, regarding the actual problem,

Greatest, To_Date, NVL
Re: Greatest Date [message #416510 is a reply to message #416505] Mon, 03 August 2009 03:02 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Please provide the exact query...as per the input text and output text
Re: Greatest Date [message #416512 is a reply to message #416510] Mon, 03 August 2009 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68727
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 03 August 2009 08:35
Show us what you tried.
Before 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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Re: Greatest Date [message #416513 is a reply to message #416510] Mon, 03 August 2009 03:07 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Please provide exact test case ("create table" and "insert" statements for the data)

Re: Greatest Date [message #416517 is a reply to message #416513] Mon, 03 August 2009 03:21 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
CREATE TABLE TEST2
(
Name VARCHAR2(12 BYTE),
A_LAST_USED DATE,
B_LAST_USED DATE,
MAY09 DATE,
JUN09 DATE
)

INSERT INTO TEST2 ( NAME, A_LAST_USED, B_LAST_USED, MAY09, JUN09 ) VALUES ('A', TO_Date( '07/31/2009 12:11:09 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '07/31/2009 11:57:11 AM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '05/31/2009 09:00:08 PM', 'MM/DD/YYYY HH:MI:SS AM'), TO_Date( '06/30/2009 03:07:33 PM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO TEST2 ( NAME, A_LAST_USED, B_LAST_USED, MAY09, JUN09 ) VALUES ( 'B', NULL, NULL, TO_Date( '05/25/2009 10:48:36 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL);



OUTPUT
Name LastUsed
A 31/07/2009 12:11:09
B 25/05/2009 10:48:36
Re: Greatest Date [message #416519 is a reply to message #416517] Mon, 03 August 2009 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68727
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 03 August 2009 10:07
Michel Cadot wrote on Mon, 03 August 2009 08:35
Show us what you tried.
Before 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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel



Re: Greatest Date [message #416521 is a reply to message #416519] Mon, 03 August 2009 03:51 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
SQL> SELECT name,
  2         Greatest(Nvl(A_LAST_USED,
  3                      To_Date('01.01.0001','dd.mm.yyyy')),
  4                  Nvl(B_LAST_USED, 
  5                      To_Date('01.01.0001','dd.mm.yyyy')),
  6                  Nvl(MAY09      , 
  7                      To_Date('01.01.0001','dd.mm.yyyy')),
  8                  Nvl(JUN09     , 
  9                      To_Date('01.01.0001','dd.mm.yyyy'))
 10         ) LAST_USED
 11  FROM TEST2;

NAME         LAST_USED
------------ -------------------
A            31.07.2009 12:11:09
B            25.05.2009 10:48:36

SQL>


Now please go and read the forum guide on how to format your posts.
Re: Greatest Date [message #416527 is a reply to message #416521] Mon, 03 August 2009 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68727
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As this has already been said to him numerous times and he nevertheless get a full solution without making any effort to follow the guide or find the answer, I bet the next post will not be formated.

Note that the guide states that full solution should not be given in such case.

Regards
Michel
Re: Greatest Date [message #416539 is a reply to message #416527] Mon, 03 August 2009 05:09 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yeah, I was kinda torn on this one.

But since he managed what a lot of people didn't manage lately, namely posting a working test case (albeit unformatted) I gave him a last chance.
Re: Greatest Date [message #416588 is a reply to message #416521] Mon, 03 August 2009 07:19 Go to previous message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Thanks It's working fine !!!
Previous Topic: BULK COLLECT -- FOR ALL Exceptions
Next Topic: how can we create table by using self-join?
Goto Forum:
  


Current Time: Sat Dec 14 02:11:56 CST 2024