ORACLE: SQL Query to extract last entry date per category

Question

By this query

select  cust_id, start_time, category from (
select * from events order by category  asc
)where cust_id= '860730'
AND start_time BETWEEN TO_DATE('07/11/2020 00:00:00', 'DD/MM/YYYY hh24:mi:ss') AND TO_DATE('08/11/2020 00:00:00', 'DD/MM/YYYY hh24:mi:ss')

I get:

    cust_id    start_time      category
    860730  07-NOV-20 07:04:00  1
    860730  07-NOV-20 08:40:36  1
    860730  07-NOV-20 08:49:03  1
    860730  07-NOV-20 08:56:22  1
    860730  07-NOV-20 07:45:53  2
    860730  07-NOV-20 07:56:44  2
    860730  07-NOV-20 08:49:15  2
    860730  07-NOV-20 08:59:37  2
    860730  07-NOV-20 09:10:52  2
    860730  07-NOV-20 11:34:49  3
    860730  07-NOV-20 08:37:10  3
    860730  07-NOV-20 09:00:21  3
    860730  07-NOV-20 13:05:53  3
    860730  07-NOV-20 15:55:34  3
    860730  07-NOV-20 16:07:46  3
    860730  07-NOV-20 16:47:08  4
    860730  07-NOV-20 17:37:27  4
    860730  07-NOV-20 18:59:35  4

How can I extract only the most recent records by date per category? To get at the end this result:

860730  07-NOV-20 08:56:22  1
860730  07-NOV-20 09:10:52  2
860730  07-NOV-20 16:07:46  3
860730  07-NOV-20 18:59:35  4

Answer 1

The basic answer to your question is to use row_number(). But note the other changes I've made to the query:

select cust_id, start_time, category 
from (select e.*,
             row_number() over (partition by category order by fme_start_time desc) as seqnum
      from events e
      where cust_id = '860730' and
            fme_start_time >= date '2020-11-07' and
            fme_start_time < date '2020-11-08'
     ) e
where seqnum = 1;

Notes:

• Oracle supports date literals. This is more concise and accurate than converting strings to dates.
• All the where conditions can go in the subquery.
• row_number() does what you want.
• if cust_id is a number -- which I strongly suspect -- drop the single quotes. Compare numbers to numbers and strings to strings. Don't mix types.

EDIT:

Actually, given the columns you want, aggregation is simpler:

select cust_id, max(start_time), category 
from events e
where cust_id = '860730' and
       fme_start_time >= date '2020-11-07' and
       fme_start_time < date '2020-11-08'
group by cust_id, category;

The first version is handy if there are other columns you want.