0

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
OutOfMemoryError
  • 391
  • 2
  • 4
  • 16

1 Answers1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • SITE_ID NOT NULL CHAR(6) – OutOfMemoryError Dec 09 '20 at 12:55
  • 1
    This is a duplicate of your answer [here](https://stackoverflow.com/a/32585555/1509264). Please don't answer duplicates but use your close votes instead. – MT0 Dec 09 '20 at 12:59
  • @Gordon Linoff: but if I want to keep the time entirely '07/11/2020 00:00:00', 'DD/MM/YYYY hh24:mi:ss' how can change the query? – OutOfMemoryError Dec 09 '20 at 16:07
  • You can use `timestamp` to provide a constant with a time. – Gordon Linoff Dec 09 '20 at 16:08
  • @GordonLinoff: instead of date? fme_start_time >= date '2020-11-07' to become fme_start_time >= timestamp '2020-11-07' because my database keep the hours – OutOfMemoryError Dec 09 '20 at 16:15
  • If your comparison values do not have time components, then `date` is fine for the constants. – Gordon Linoff Dec 09 '20 at 16:16
  • @GordonLinoff: I mean I want to keep hours and minutes and seconds because the date could be not 00:00:00. I put so to clarify – OutOfMemoryError Dec 09 '20 at 16:18
  • @GordonLinoff: it has timestamp. I don't want to truncate the hours/time/minutes... how it will change? the query? thanks ... sorry for this request. – OutOfMemoryError Dec 09 '20 at 16:20
  • @GordonLinoff: in fact on my request there is this: 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 want to keep that in final query – OutOfMemoryError Dec 09 '20 at 16:23
  • @OutOfMemoryError . . . You can keep the query. I recommend using explicit date and timestamp literals, as this answer proposes. The absence of a time on a literal in the query has nothing to do with the data in the table. – Gordon Linoff Dec 09 '20 at 16:24
  • @GordonLinoff but if the time of the range start so? example: 08/11/2020 12:23:44 how can manage it? – OutOfMemoryError Dec 09 '20 at 16:26
  • @GordonLinoff: if I do AND fme_start_time >= timestamp '07/11/2020 12:44:15' or AND fme_start_time >= date'07/11/2020 12:44:15' doesn't work. Can you please change the query or show me? Using BETWEEN doesn't work aswell – OutOfMemoryError Dec 09 '20 at 16:29
  • to_timestamp('07/11/2020 00:00:00', 'DD/MM/YYYY hh24:mi:ss') fixed it. Thank you – OutOfMemoryError Dec 09 '20 at 16:35