mysql, after an event occurs how to get relevant data from last three time periods

Question

I select a table like below filtered for when a data value is larger than x, call this the event. Now I want to grab the data for last three times that the unique (id1, id2) pair were in my database before the event occurred.

  example table 1 (after filtering for data>x)
 id1     id2   time data 
  76    1234   t101   d1
  46    9573   t102   d2 
  24    3632   t103   d3
  88    4792   t104   d4
  17    7799   t105   d5
  74    2274   t106   d6

    example output table
    id1     id2 time data 
    76    1234   t101   d1*
    76    1234   t76    d2
    76    1234   t44    d3
    76    1234   t32    d4
    46    9573   t102   d5 *
    46    9573   t99    d6 
    46    9573   t98    d7  
    46    9573   t91    d8 

So far I have considered id1*id2 to generate a new unique id: id1_id2 on which to join. To get something like "example output table" but with all the data for id1_id2, not just the three data entries prior the event.

Answer 1

This might be what you want, depending on clarifications:

SELECT numbered.*
FROM SELECT filtered.*,
            IF (id12 = @prev,
                @n := @n + 1,
                @n := 1 AND @prev := id12) as position
     FROM
          (SELECT     mytable.*, concat_ws('_', id1, id2) as id12
          FROM       (SELECT id1, id2, time FROM mytable WHERE data > x) AS mytablex
          INNER JOIN mytable
          ON         mytable.id1 = mytablex.id1 AND mytable.id2 = mytablex.id2
          ORDER BY   id1, id2, time DESC)
          AS         filtered
     JOIN (SELECT @n := 0, @prev := '') AS setup)
     AS   numbered
WHERE numbered.position < 4