How to compare each row from the table with all rows?

Im using mysql.

Compare each row with all the left rows from this table and select the identifier and timestamp, where HEADING is between -15 <Title (from the last row) +15 AND -10 <timestamp (from the last row) <+10.

myTable id heading (In degrees) timestamp (in seconds) 

Eg. from the first rows of the table:

 1 - 50 - 133324565 2 - 12 - 133324565 3 - 10 - 133324565 5 - 220 - 133324565 6 - 35 - 133324565 10 - 10 - 133324565 11 - 15 - 133324565 12 - 22 - 133324565 16 - 168 - 133324565 

As you can see, the identifiers are not sequential, so I can not do it right?

 SELECT T1.id,T1.heading,T2.id,T2.heading FROM mytable as T1, mytable as T2 WHERE T1.id > T2.id -1 AND (T1.timestamp > T2.timestamp -10 OR T1.timestamp < T2.timestamp +10) AND (T1.heading > T2.heading -15 OR T1.heading < T2.heading +15) 
+4
source share
1 answer
 SELECT T1.id,T1.heading,T2.id,T2.heading FROM mytable as T1, mytable as T2 WHERE T1.id > T2.id AND NOT EXISTS (SELECT T3.id FROM mytable as T3 WHERE T3.id<T1.id AND T3.id>T2.id) AND (T1.timestamp > T2.timestamp -10 OR T1.timestamp < T2.timestamp +10) AND (T1.heading > T2.heading -15 OR T1.heading < T2.heading +15) 
+1
source

Source: https://habr.com/ru/post/1485794/


All Articles