-1

I have the following simple select statement:

SELECT ID, EVENT, TIMESTAMP from table
order by ID, TIMESTAMP;

I now want to get for every ID only the entry with the last timestamp, i.e. with the max(TIMESTAMP). How can I get this? Do I have to use a subquery?

MT0
  • 143,790
  • 11
  • 59
  • 117
Tobitor
  • 1,388
  • 1
  • 23
  • 58

1 Answers1

2

One method uses aggregation:

select id, max(timestamp) as timestamp,
       max(event) keep (dense_rank first order by timestamp desc) as event
from t
group by id;

The keep syntax is Oracles (rather verbose) way of implementing a "first" aggregation function.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • "rather verbose" - as opposed to what other way? I believe most other db products don't implement the first/last aggregate (and analytic) functions at all, never mind the syntax. –  Sep 01 '21 at 13:16