MySQL: Get direct siblings of the row

Question

I have problem to select direct siblings of the main row. For example this is my data I need to filter:

+------+------------+------------+
| id   | from       | to         |
+------+------------+------------+
| 2265 | 2016-03-30 | 2016-04-09 |
| 1420 | 2016-03-30 | 2016-04-11 |
| 2261 | 2016-03-30 | 2016-04-12 |
| 2262 | 2016-04-01 | 2016-04-12 |
| 1296 | 2016-04-01 | 2016-04-24 |
| 1053 | 2016-04-01 | 2016-05-01 |
| 2302 | 2016-04-30 | 2016-05-24 |
| 2025 | 2016-05-14 | 2016-06-06 |
| 2392 | 2016-05-20 | 2016-05-28 |
| 2033 | 2016-05-21 | 2016-06-08 |
| 2389 | 2016-05-25 | 2016-06-09 |
+------+------------+------------+

All I need is to select row and it's direct siblings by min/max dates. For example, result should be:

+------+------------+------------+
| id   | from       | to         |
+------+------------+------------+
| 1296 | 2016-04-01 | 2016-04-24 |
| 1053 | 2016-04-01 | 2016-05-01 |
| 2302 | 2016-04-30 | 2016-05-24 |
+------+------------+------------+

Where row 1053 is the "main" row.

I know it's something with MIN and MAX functions, but I can't figure how to use it. I could do it in my app, but I'd rather do it in SQL because of performance.

score 1 · Accepted Answer · answered Mar 30 '16 at 08:06

1

It seems you want something like the following query:

SELECT m.*
FROM mytable AS m
JOIN (
  SELECT id, 
         (SELECT id 
          FROM mytable AS t2
          WHERE t1.id <> t2.id AND t1.`to` >= t2.`to`
          ORDER BY `to` DESC, `from` DESC LIMIT 1) AS prev_id,
         (SELECT id 
          FROM mytable AS t3
          WHERE t1.id <> t3.id AND t1.`to` <= t3.`to`
          ORDER BY `to`, `from` LIMIT 1) AS next_id        
  FROM mytable AS t1
  WHERE id = 1053
) AS t ON m.id IN (t.id, t.prev_id, t.next_id)

The above query uses two correlated subqueries in order to get previous and next records with regard to the record having the specified id value.

Demo here

answered Mar 30 '16 at 08:06

Giorgos Betsos

71,379
9
63
98

I think you are correct, but I need to test it. There is one record with ``to`` value less than previous row, but it's below because of ``from`` date. So I guess I should omit ``to`` value as long as ``from`` is different. – p3le Mar 30 '16 at 08:39
Don't know how to edit comments, so I'll post another.. I did a little update to your answer with conditional statement and querying by ``from`` value which is more important than ``to``. [Edited demo here](http://sqlfiddle.com/#!9/02d83/23) – p3le Mar 30 '16 at 08:53

score 1 · Answer 2 · answered Mar 30 '16 at 08:15

Here is a method utilizing user variable.

select t1.id, t1.from, t1.to
from 
    (select *, @rn1 := @rn1 + 1 as row_num from t 
        cross join (select @rn1 := 0) p1) t1
    cross join (select @target := 
        (select t2.row_num 
        from (select *, @rn2 := @rn2 + 1 as row_num from t 
            cross join (select @rn2 := 0) p2) t2
        where t2.id = 1053)
    ) p3
where t1.row_num in (@target-1, @target, @target+1);

To use it on your table, replace t to your own table name.

MySQL: Get direct siblings of the row

2 Answers2