How do I create a join using a 'greater than' and a 'group by'?

Question

I have a table containing a series of names, events and dates. I've created a new field 'evt5_date' which is related to a specific event (evt5).

Each name can have several events the timing of each is recorded in evt_date field. Two events evt1 and evt2 are related to evt5.

I want to insert the date of the first occurrence of an evt5 into all evt1 and evt2 rows preceding the evt5. If there is no evt5 after the evt1 or evt2 then the field is left empty.

All this must be done for each name. There are a few thousand different names. I've only show 2 in the data below

Current table data - no values in evt5_date

name      evt_date       event    evt5_date
name-1    2010-06-30     evt1     
name-1    2009-10-30     evt5
name-1    2009-09-30     evt2      
name-1    2009-06-30     evt5          
name-1    2009-03-30     evt5     
name-1    2009-02-28     evt2     
name-1    2009-01-30     evt1     
name-2    2005-05-30     evt2
name-2    2005-03-30     evt5
name-2    2005-01-30     evt1     

How I'd like it to look - values in evt5_date field

name      evt_date       event    evt5_date
name-1    2010-06-30     evt1     
name-1    2009-10-30     evt5
name-1    2009-09-30     evt2     2009-10-30 
name-1    2009-06-30     evt5          
name-1    2009-03-30     evt5     
name-1    2009-02-28     evt2     2009-03-30
name-1    2009-01-30     evt1     2009-03-30
name-2    2005-05-30     evt2
name-2    2005-03-30     evt5
name-2    2005-01-30     evt1     2005-03-31

I attempted to perform the update with the code below, but I didn't know how to specify the linkage between the date of evt5 being greater than the evt_date of evt1 and evt2 while also grouping by the evt5 in order to obtain the evt_date relating to the most recent evt5.

I also need to group by name since the events are specific to each name.

update mytable as t1
set t1.evt5_date = (select min(t2.evt_date) from mytable as t2
                    where t2.event = 'evt5' AND
                           t2.evt_date > t1.evt_date
                    group by name)
where
  t1.event in ('evt1', 'evt2')

Any suggestions would be greatly appreciated. Thanks

Update final solution - some minor changes to answer provided by @biziclop to maintain the integrity of the name

UPDATE mytable AS t1
INNER JOIN
    (
      SELECT
             a.name, a.evt_date,
        MIN( b.evt_date ) AS nearest_date
      FROM       mytable AS a
      INNER JOIN mytable AS b ON b.event = 'evt5'
                            AND b.evt_date > a.evt_date
                            AND a.name = b.name       -- needed this additional condition
      GROUP BY a.name, a.evt_date                     -- added 'a.name' to 'group by' 
    ) AS nearest_dates 
       ON nearest_dates.evt_date = t1.evt_date AND
         nearest_dates.name = t1.name                 -- added this additional condition
SET t1.evt5_date = nearest_dates.nearest_date
WHERE t1.event IN ('evt1', 'evt2');

Solution

I fixed your update statement, I think it works now. I omitted the GROUP BY name part, because it wasn't meaningful in your version.

UPDATE mytable AS t1
INNER JOIN
    (
      SELECT
        a.evt_date,
        MIN( b.evt_date ) AS nearest_date
      FROM       mytable AS a
      INNER JOIN mytable AS b ON b.event = 'evt5'
                            AND b.evt_date > a.evt_date
      GROUP BY a.evt_date
    ) AS nearest_dates ON nearest_dates.evt_date = t1.evt_date
SET t1.evt5_date = nearest_dates.nearest_date
WHERE t1.event IN ('evt1', 'evt2');

First some verifications: http://sqlfiddle.com/#!2/309ac/6

The final UPDATE query: http://sqlfiddle.com/#!2/80c3c/1

Comments

[getting-there]

thanks a lot for this and for the great explanation on sqlfiddle. I made some minor changes to account for the varying names (as shown in the updated question). I really appreciate the help.