Clever Geek Handbook

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

I'm trying to group results by date, but at the same time I would like to display a timestamp. I have a table as shown 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 have to get results like ...

 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 get duplicates as the time is different in seconds. I cannot use First_Value with PARTITION as there is no unique id, all 4 columns have uniqueness. Please let me know how to achieve this. Thank you.

+3


source to share


1 answer


Try the following:

 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


section trunc(datetme)

to solve the time problem.

+1


source






More articles:


All Articles