CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
max
max

Reputation: 23

Find highest Value among duplicates in a table (Oracle)

I have a table with duplicate values as shown below.

enter image description here

I would like to find the latest start time among the events. Expected output is

enter image description here

I used the below query but it seems to get the latest start time in the entire table.

SELECT ID,
EVENT,
START_TIME,
LAST_VALUE(START_TIME) OVER (ORDER BY ID,EVENT RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_start_time
FROM
(Select * from EVENTS)
order by ID,EVENT;

I know I am missing something, probably a group by. Can you please help me out. I am using ORACLE.

Upvotes: 0

Views: 319

Answers (2)

MT0
MT0

Reputation: 167774

You just need the MAX analytic function and partition on id and event.

SELECT ID,
       EVENT,
       START_TIME,
       MAX(START_TIME) OVER (PARTITION BY id, event) AS latest_start_time
FROM   EVENTS
order by ID,EVENT;

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

Try

SELECT DISTINCT
   ID,
   EVENT,
   START_TIME,
   MAX(START_TIME) OVER (PARTITION BY ID, EVENT) AS latest_start_time
FROM EVENTS
order by ID,EVENT;

Upvotes: 1

Related Questions