-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
  • 113,669
  • 10
  • 50
  • 103
Tobitor
  • 1,080
  • 9
  • 29

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,198,228
  • 53
  • 572
  • 709
  • "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. – mathguy Sep 01 '21 at 13:16