How to group by date and also get time / how to get first occurence of record Oracle

Question

I am trying to group by the results on date but same time would like to display the Timestamp. I have a table like below ...

 DateTme               col1      col2       col3     col4 
 12/16/2014 3:23        we        23         xyz      R
 12/16/2014 5:14        re        45         uty      X
 12/16/2014 5:15        re        45         uty      X
 12/16/2014 5:47        ue        87         owu      B
 12/16/2014 6:30        oe        92         pqr      M
 12/16/2014 6:33        oe        92         pqr      M  

I should get the results as ...

 DateTme               col1      col2       col3     col4 
 12/16/2014 3:23        we        23         xyz      R
 12/16/2014 5:15        re        45         uty      X
 12/16/2014 5:47        ue        87         owu      B
 12/16/2014 6:33        oe        92         pqr      M  

If I group by date, col1, col2, col3 col4 I am getting duplicates as the time is differ in seconds. I can't use the First_Value with PARTITION as there is no unique ID , the uniqueness comes with all 4 columns. Please let me know how to achieve this. Thanks.

Answer 1

Try this:

 select datetme,col1,col2,col3,col4 from (
 select t1.*,row_number() over (partition by trunc(datetme),col1,col2,col3,col4 
 order by datetme desc) cont from your_table t1)
 where cont = 1
 order by datetme

partition by trunc(datetme) solve the time problem.