CODEWITHSUNDEEP
mysqlsqljoinself-join
balu
balu

Reputation: 3

mysql selfjoin on minimal difference

I have a question corresponding SQL / Mysql join operation.

My database table structure has the following fields

time timestamp

lat double

lon double

So the actual data looks like this

| 2013-07-20 16:32:22 | 49.3 | 8.3 |

| 2013-07-20 16:17:09 | 49.2 | 8.2 |

| 2013-07-20 15:58:19 | 49.1 | 8.1 |

I now want to join this table with its self, so that one dataset is joind with the one having the next lower timestamp. So that the difference between thoose two is minimal.

So in the end my result would be

| 2013-07-20 16:32:22 | 49.3 | 8.3 | 2013-07-20 16:17:09 | 49.2 | 8.2 |

| 2013-07-20 16:17:09 | 49.2 | 8.2 | 2013-07-20 15:58:19 | 49.1 | 8.1 |

To get there i tried the folloing SQL statement

SELECT * FROM position p1 , position p2 WHERE p1.time > p2.time GROUP BY p1.time;

But the result was not exactly what i intended, the secound (grouped by) part is not sorted 'correctly' by this solution. It looks like this:

| 2013-07-20 16:32:22 | 49.3 | 8.3 | 2013-07-20 15:58:19 | 49.1 | 8.1 |

| 2013-07-20 16:17:09 | 49.2 | 8.2 | 2013-07-20 15:58:19 | 49.1 | 8.1 |

Can anybody tell me if my intended result is possible by using SQL ?

Upvotes: 0

Views: 55

Answers (2)

contradictioned
contradictioned

Reputation: 1253

This is also a possibility which is a little more 'readable' in my opinion:

select 
  p1.time as time1,
   (select min(p2.time) as time2
    from position p2
    where p2.time > p1.time)
from position p1

But note, that this readability comes with quadratic complexity since the subquery would be executed for every row of p1. If your table is large enough you should go with the code of jtseng.

PS: Sqlfiddle for looking at the query plan: http://sqlfiddle.com/#!2/d777f/10

PPS: With the other fields included this could look like:

select 
  p1.time as time1,
  p1.lat as lat1,
  p1.lon as lon1,
  p2.time as time2,
  p2.lat as lat2,
  p2.lon as lon2
from
  position p1,
  position p2
where
  p2.time = (select min(p.time)
   from position p
   where p.time > p1.time)

still better readability, but also the dependent subquery is issued for every row in p1.

Upvotes: 1

John Tseng
John Tseng

Reputation: 6352

This is indeed possible. Unfortunately, MySQL is missing features that makes this easy.

I would rank the rows, and then join by the rank:

select 
  p1.time as time1,
  p1.lat as lat1,
  p1.lon as lon1,
  p1.rank as rank1,
  p2.time as time2,
  p2.lat as lat2,
  p2.lon as lon2,
  p2.rank as rank2
from (
  select position.*,
    @curRank1 := @curRank1 + 1 AS rank 
  from position , (SELECT @curRank1 := 0) r 
  order by time desc) p1
join (
  select position.*,
    @curRank2 := @curRank2 + 1 AS rank 
  from position , (SELECT @curRank2 := 0) r 
  order by time desc) p2
on p1.rank = p2.rank -1

Here's the fiddle showing it in action: http://sqlfiddle.com/#!2/d777f/8

Upvotes: 1

Related Questions