SQL Statement to Return Records with More than Two Instances and Omit the Last Instance-CodePudding

I am looking to write a SQL statement that will pull EventIDs with two or more instances, but will omit the last instance of these records. This seems crazy, but the purpose of this is to look at events that have multiple updates (the updates are related to the TIME_OF_EVENT column, each time a crew/person updates the event, the time it was updated gets stored here) and see which ones have expired in the middle. I see if they expired in the middle by comparing the TIME_OF_EVENT to the PREV_ERT.

select *
from ert_change_log
where time_of_event > '30-SEP-21 23:59:59'
and source <> 'I'

The SQL above generates the picture below. This is simply just a reference of the table to provide an example of EventIDs that meet this criteria.

In a perfect world, the query I am needing would only return EventIDs 210043901 and 210044021 and would omit the latest TIME_OF_EVENT for those EventID.

If this is confusing I would be glad to offer more explanation or clarification!

Thanks for any input.

CodePudding user response：

You can use the ROW_NUMBER analytic function:

SELECT *
FROM   (
  SELECT e.*,
         ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY time_of_event DESC) AS rn
  FROM   ert_change_log e
  WHERE  time_of_event >= DATE '2021-10-01'
  AND    source <> 'I'
)
WHERE  rn > 1;