Geek asks and answers

How to create a connection using more than and group?

I have a table containing a series of names, events and dates. I created a new field ' evt5_date' that is associated with a specific event ( evt5).

Each name can have several events, the time of each of which is recorded in the field evt_date. Two events evt1and evt2related evt5.

I want to insert the date of the first occurrence evt5in all lines evt1and evt2preceding evt5. If after evt1or evt2not evt5, then the field remains empty.

All of this must be done for everyone name. There are several thousand different names. I 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 would like to look - the values ​​in the 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 tried to upgrade using the code below, but I did not know how to specify a relationship between the evt5 date greater than evt_date from evt1 and evt2, as well as the evt5 grouping, to get evt_date related to the last evt5.

I also need to group by name, as 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 the answer provided by @biziclop to maintain integrityname

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');
+5
source share
1 answer

, , . GROUP BY name, .

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');

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

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

+4

More articles:


All Articles