Home » SQL & PL/SQL » SQL & PL/SQL » Select max date in row of columns. . . (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Select max date in row of columns. . . [message #637684] Thu, 21 May 2015 11:38 Go to next message
fluffycone
Messages: 27
Registered: June 2011
Junior Member
I am trying to develop an SQL statement where I can select the max Date value for each row. The format might look like this:

2292, 29-May-14
2427, 22-May-12
0878, 12-Jun-12

So the table with 4 columns would drop to a table with two columns with user_id and max data value of the three columns.

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


 CREATE TABLE "MAXDATEINROW_T" 
   (	"USER_ID" VARCHAR2(16 BYTE), 
	"A_DATE" DATE, 
	"B_DATE" DATE, 
	"C_DATE" DATE
   ) ;


Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('2292',to_date('29-MAY-14','DD-MON-RR'),null,null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('2427',null,to_date('22-MAY-12','DD-MON-RR'),null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('0878',to_date('12-JUN-12','DD-MON-RR'),null,null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('2720',to_date('30-APR-13','DD-MON-RR'),to_date('27-MAY-10','DD-MON-RR'),null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('6379',to_date('08-MAY-12','DD-MON-RR'),null,null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('2525',to_date('29-MAY-14','DD-MON-RR'),null,to_date('29-MAY-14','DD-MON-RR'));
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('2002',to_date('24-MAY-12','DD-MON-RR'),null,null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('1162',to_date('20-JUN-11','DD-MON-RR'),to_date('09-JUN-14','DD-MON-RR'),null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('7676',null,to_date('15-MAY-12','DD-MON-RR'),null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('2782',null,to_date('19-MAY-09','DD-MON-RR'),to_date('15-JUN-12','DD-MON-RR'));
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('0137',to_date('05-FEB-10','DD-MON-RR'),to_date('14-MAY-13','DD-MON-RR'),to_date('14-MAY-13','DD-MON-RR'));
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('2078',null,null,null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('2078',null,null,null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('2078',null,null,null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('4197',null,null,null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('4197',null,null,null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('2576',to_date('28-APR-09','DD-MON-RR'),to_date('28-APR-09','DD-MON-RR'),to_date('24-MAY-13','DD-MON-RR'));
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('4692',to_date('22-MAY-12','DD-MON-RR'),null,null);
Insert into MAXDATEINROW_T (USER_ID,A_DATE,B_DATE,C_DATE) values ('3059',to_date('28-MAY-10','DD-MON-RR'),to_date('02-MAY-13','DD-MON-RR'),null);
Re: Select max date in row of columns. . . [message #637685 is a reply to message #637684] Thu, 21 May 2015 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when all else fails Read The Fine Manual

https://docs.oracle.com/database/121/SQLRF/functions077.htm#sthref1462
Re: Select max date in row of columns. . . [message #637688 is a reply to message #637685] Thu, 21 May 2015 12:26 Go to previous messageGo to next message
fluffycone
Messages: 27
Registered: June 2011
Junior Member
Thanks BlackSwan.

For all, this is the solution I was able to come up with base on BlackSwan's suggestion. If all the columns are null, then the default date is set to 1900-01-01.

select user_id, 
 greatest(
   nvl(a_date,to_date('1990-01-01', 'YYYY-MM-DD')), 
   nvl(b_date,to_date('1990-01-01', 'YYYY-MM-DD')), 
   nvl(c_date,to_date('1990-01-01', 'YYYY-MM-DD'))) as MaxDate
from maxDateInRow_t;


Now I need to figure out how to return null when all the columns are null.

And BTW, my example included 3 columns, but I will have to do this for 19 columns (Ouch).

Thanks again.

[Updated on: Thu, 21 May 2015 12:28]

Report message to a moderator

Re: Select max date in row of columns. . . [message #637689 is a reply to message #637688] Thu, 21 May 2015 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Now I need to figure out how to return null when all the columns are null.
Why is TO_DATE in your code?

I am not clear on what the problem is.
  1* select greatest(null,null), nvl(greatest(null,null),0) from dual
SQL> /

G NVL(GREATEST(NULL,NULL),0)
- --------------------------
                           0

[Updated on: Thu, 21 May 2015 12:32]

Report message to a moderator

Re: Select max date in row of columns. . . [message #637692 is a reply to message #637688] Thu, 21 May 2015 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

select user_id, 
 nullif(
 greatest(
   nvl(a_date,to_date('1990-01-01', 'YYYY-MM-DD')), 
   nvl(b_date,to_date('1990-01-01', 'YYYY-MM-DD')), 
   nvl(c_date,to_date('1990-01-01', 'YYYY-MM-DD'))),
 to_date('1990-01-01', 'YYYY-MM-DD')) as MaxDate
from maxDateInRow_t;

Re: Select max date in row of columns. . . [message #637695 is a reply to message #637688] Thu, 21 May 2015 14:01 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
fluffycone wrote on Thu, 21 May 2015 12:26


And BTW, my example included 3 columns, but I will have to do this for 19 columns (Ouch).

Thanks again.


It's just a matter of coding. Any good text editor can take care of that in just a few seconds. No need to hand-type the whole thing. Yeah, it will look a bit kludgey, but if well formatted will still be easy to read and understand.

Perhaps this is the price for not having properly normalized your data model?
Previous Topic: string aggregation
Next Topic: Need Help on query
Goto Forum:
  


Current Time: Tue Mar 19 03:12:14 CDT 2024