Link to home
Start Free TrialLog in
Avatar of chickanna
chickannaFlag for Afghanistan

asked on

oracle

Experts.... I have a table which has 3 timestamp columns. I have to select rows from it where the timestamp in one of the 3 columns is the latest. ie. let's say that the table has these columns

a  number   not null
b  varchar2(30)  null
c  datetime null
d  datetime  null
e  datetime  null
f   number  null
g  varchar2(50)  null

I need to select rows where the timestamps in c,d or e is the latest. c,d and e may have data or may not have datetime values as they are declared as null columns. Do I have to do this using CASE statements or is there a better way.
Avatar of sventhan
sventhan
Flag of United States of America image

CREATE TABLE t (
datecol1 DATE,
datecol2 DATE,
datecol3 DATE)
PCTFREE 0;

INSERT INTO t VALUES (SYSDATE+23, SYSDATE-10, SYSDATE-24);
INSERT INTO t VALUES (SYSDATE-15, SYSDATE, SYSDATE+15);
INSERT INTO t VALUES (SYSDATE-7, SYSDATE-18, SYSDATE-9);
COMMIT;

SELECT * FROM t;

SELECT GREATEST(datecol1, datecol2, datecol3)
FROM t;

http://psoug.org/reference/date_func.html
Avatar of chickanna

ASKER

but if one of the columns has no value as it is defined as null columns then the above query will return empty in the result. I want the greatest of the timestamps even if there are nulls or no value in any of the columns.
Not sure but can you try this?

SELECT   GREATEST (NVL (datecol1, TO_DATE ('01/01/1900', 'MM/DD/YYYY')),NVL (datecol2, TO_DATE ('01/01/1900', 'MM/DD/YYYY')), NVL (datecol3, TO_DATE ('01/01/1900', 'MM/DD/YYYY'))) from t

Open in new window

Use as not null condition if you don't want to view the null records:

SELECT GREATEST(DATECOL1, DATECOL2, DATECOL3)
FROM T WHERE DATECOL1 IS NOT NULL
AND DATECOL2 IS NOT NULL
and DATECOL3 is not null;


use this way for timestamp:

INSERT INTO t VALUES (SYSTIMESTAMP+23, SYSTIMESTAMP-10, SYSTIMESTAMP-24);
INSERT INTO t VALUES (SYSTIMESTAMP-15, SYSTIMESTAMP, SYSTIMESTAMP+15);
INSERT INTO T VALUES (SYSTIMESTAMP-7, SYSTIMESTAMP-18, SYSTIMESTAMP-9);
COMMIT;

SELECT * FROM t;


SELECT TO_DATE(GREATEST(DATECOL1, DATECOL2, DATECOL3),'DD-MON-YYYY HH24:MI:SS') AS GRETEST_DATE
FROM T
WHERE DATECOL1 IS NOT NULL
AND DATECOL2   IS NOT NULL
AND DATECOL3   IS NOT NULL;
ajexpert--- can i put a null value instead of 1/1/1900 as records with all null values in the 3 timestamp columns will get this 1/1/1900 value instead of null
ASKER CERTIFIED SOLUTION
Avatar of ajexpert
ajexpert
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial