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

Question

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

question from:https://stackoverflow.com/questions/65546312/how-to-write-select-distinct-that-works-on-date-fields-with-time-differences

Answer 1

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...