CODEWITHSUNDEEP
mysqlsqlrange
haroon latif
haroon latif

Reputation: 53

MySQL updating field based on datetime range from the same table

I have a table like 

datetime             game   sum         Releated
01/03/2015 00:00:25  Game1  1342.75 
01/03/2015 00:01:52  Game2  1418.5  
01/03/2015 00:01:53  Game3  1289    
01/03/2015 00:04:41  Game4  1473.25 
01/03/2015 00:06:09  Game5  1581.25

to become something like this. 

 datetime            game   sum         Releated
01/03/2015 00:00:25  Game1  1342.75 
01/03/2015 00:01:52  Game2  1418.5      01/03/2015 00:01:52
01/03/2015 00:01:53  Game3  1289        01/03/2015 00:01:52
01/03/2015 00:04:41  Game4  1473.25 
01/03/2015 00:06:09  Game5  1581.25

If two or more rows are within 10 seconds time difference (e.g: 2nd and 3rd row) I want to update related columns to mark it. Either with datetime or some value. Which mean that are the same row set.

or

Based on the same theory I want to sum the sum column if they are between 10 seconds range.

Upvotes: 2

Views: 177

Answers (2)

shA.t
shA.t

Reputation: 16958

Try this:

SELECT *,
  (SELECT min(t2.d) 
   FROM yourTable t2
   WHERE ABS(TIMESTAMPDIFF(SECOND,t2.d, t1.d)) <= 10
         AND t2.d <> t1.d
  ) As related
FROM yourTable t1;

Upvotes: 1

javier_domenech
javier_domenech

Reputation: 6253

SELECT * FROM harronLati a inner join harronLati b
WHERE
ABS(TIMESTAMPDIFF(SECOND,a.datetime,b.datetime)) <= 10 
group by a.datetime,a.game,a.sum,a.Releated

Upvotes: 0

Related Questions