Learning SQL Learning SQL - 2 months ago 5x
SQL Question

SQL Join one to many extract value from group of same key value where condition is met

I have two tables with a one to many relationship i'm trying to join. The parent table has a unique key value shared with the child table. The child table keeps history for that key so there is multiple records. Example below.

What I need is to extract the worker name for an alert id where the action is 'Alert Closed' for a given group of the same alert id. If that group of same alert id doesn't have a record with action = 'Alert Closed' than take the worker name with the most recent timestamp. I'm lacking the knowledge on how to compare timestamp columns also I get duplicates still when 'Alert Closed' is true.

Table 1


Table 2

alert_id worker_name action timestamp
123 system Alert Created 8/6/2016 8:05:26
123 james bond Alert Opened 8/6/2016 8:05:30
123 james bond Alert Closed 8/6/2016 8:05:35
123 james bond Record updated 8/6/2016 8:05:35
456 system Alert Created 8/6/2016 8:05:26
456 admin Alert updated 8/6/2016 8:06:14
537 system alert created 8/6/2016 8:07:20
537 Mary hill Alert Closed 8/6/2016 8:08:26

Resulting table should be:

alert_id worker_name
123 james bond
456 admin
537 Mary Hill


This is a prioritization query. You can approach it using row_number(). The trick is to get the ordering right:

select t2.*
from (select t2.*,
             row_number() over (partition by alert_id
                                order by (case when action = 'Alert Closed' then 1 else 2 end),
                                         timestamp desc
                               ) as seqnum
      from t2
     ) t2
where seqnum = 1;