Group oracle table by name and order by time desc with time should be maximum for today's timestamp

Question

Suppose I have a table as below in Oracle database

ID      NAME        SAVED_ON
_____________________________
10     APPLE    10-DEC-17 03.08.26.353000000 PM
15     MANGO    10-DEC-17 05.08.26.353000000 PM
17     APPLE    10-DEC-17 04.08.26.353000000 PM
19     APPLE    10-DEC-17 02.08.26.353000000 PM
23     MANGO    10-DEC-17 01.08.26.353000000 PM 

My Requirement is : I need all the distinct id's but taking the above table as example, For Apple we have 3 entries in the table and similarly for mango we have 2 entries but I need a sql query which will give the id of the entry of the apple and mango which was saved at the last i.e maximum saved_on timing.

i.e group by name and order by saved_on and final result should be id's with max(saved_on)

The ouput should come as Id's- 15,17

Answer 1

Use an inline view to rank each fruit in descending timestamp order, which provides the filter in the outer query.

select id, name, saved_on 
from (
    select id, name, saved_on 
           , row_number() over (partition by name order by saved_on desc) as rn
    from fruit
    where saved_on >= trunc(sysdate)
)
where rn = 1
order by name, id
/

"the day needs to be filtered for today timestamp"

Assuming that saved_on is a doesn't contain future values, it's simple enough: truncating sysdate gives the date with a time of midnight.

Answer 2

You can use FIRST_VALUE in combination with DISTINCT :

 SELECT DISTINCT name,
        FIRST_VALUE(id) OVER (PARTITION BY name ORDER BY saved_on DESC) id,
        FIRST_VALUE(saved_on) OVER (PARTITION BY name ORDER BY saved_on DESC) saved_on
   FROM fruit
  ORDER BY name 

See Oracle Documentation for more informations