title: How do I create a join using a 'greater than' and a 'group by'?
body: <p>I have a table containing a series of names, events and dates. I've created a new field '<code>evt5_date</code>' which is related to a specific event (<code>evt5</code>). </p>
  
  <p>Each name can have several events the timing of each is recorded in <code>evt_date</code> field.
  Two events <code>evt1</code> and <code>evt2</code> are related to <code>evt5</code>. </p>
  
  <p>I want to insert the date of the first occurrence of an <code>evt5</code> into all <code>evt1</code> and <code>evt2</code> rows preceding the <code>evt5</code>. If there is no <code>evt5</code> after the <code>evt1</code> or <code>evt2</code> then the field is left empty. </p>
  
  <p>All this must be done for each <code>name</code>. There are a few thousand different names. I've only show 2 in the data below</p>
  
  <p><strong>Current table data</strong> - no values in evt5_date</p>
  
  <pre><code>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     
  </code></pre>
  
  <p><strong>How I'd like it to look</strong> - values in evt5_date field</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>I also need to group by name since the events are specific to each name.</p>
  
  <pre><code>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 &gt; t1.evt_date
                      group by name)
  where
    t1.event in ('evt1', 'evt2')
  </code></pre>
  
  <p>Any suggestions would be greatly appreciated. Thanks</p>
  
  <p><strong>Update final solution</strong> - some minor changes to answer provided by @biziclop to maintain the integrity of the <code>name</code></p>
  
  <pre><code>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 &gt; 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');
  </code></pre>