CODEWITHSUNDEEP
oracle-databasedatetimeselectdistinct
cmomah
cmomah

Reputation: 315

How to write SELECT DISTINCT that works on DATE fields with time differences

Does anyone know how to write the SELECT DISTINCT statement so that the rows circled in blue can be treated as duplicates? Currently, at the date level, they are duplicates but they have time differences

SELECT ID, PHN_NO, DATE_CREATED, DATE_MODIFIED FROM USER_PHONE_HISTORY
WHERE PHONE_NUMBER = '1234567890'
ORDER BY START_DATE DESC;

-- 12 RECORDS

SELECT DISTINCT ID, PHN_NO, DATE_CREATED, DATE_MODIFIED FROM USER_PHONE_HISTORY
WHERE PHONE_NUMBER = '1234567890'
ORDER BY START_DATE DESC;

-- 12 RECORDS

enter image description here

Upvotes: 0

Views: 39

Answers (1)

user5683823
user5683823

Reputation:

If I understand correctly, you probably want something like

select distinct id, phn_no, trunc(date_created)  as date_created, 
                            trunc(date_modified) as date_modified
from   user_phone_history
where  .......
order  by .......

or some simple modification thereof (it's not clear which date you must handle - this handles both).

I am not sure why you want to do this, but I assume you have your reasons...

Upvotes: 3

Related Questions