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 |
|
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 #637688 is a reply to message #637685] |
Thu, 21 May 2015 12:26 |
|
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 #637692 is a reply to message #637688] |
Thu, 21 May 2015 12:44 |
|
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 |
|
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?
|
|
|
Goto Forum:
Current Time: Tue Mar 19 03:12:14 CDT 2024
|