CODEWITHSUNDEEP
mysqlsql
Erhan
Erhan

Reputation: 188

SQL - How to select specific rows and their closest neighbors?

I have a table which I can sort by time, for example. Now I want to select my data as such as taking a row in which column_x = something and the some closest neighbors (specific number given in advance) of this row according to time. In the request I would like to select multiple rows. Let me illustrate on a simple example:

+------+----------+----------+
| Time | Column_1 | Column_2 |
+------+----------+----------+
|    1 |        0 |        5 |
|    2 |        0 |        6 |
|    3 |        1 |        9 |
|    4 |        1 |        8 |
|    5 |        0 |        5 |
|    6 |        0 |        8 |
|    7 |        0 |        9 |
|    8 |        0 |        6 |
|    9 |        0 |        4 |
|   10 |        1 |        5 |
|   11 |        0 |        2 |
|   12 |        0 |        3 |
|   13 |        0 |        1 |
+------+----------+----------+

I want to select results data such that COLUMN_1 = 1 and two closest neighbors in time. So the result I am expecting is this table:

+------+----------+----------+
| Time | Column_1 | Column_2 |
+------+----------+----------+
|    1 |        0 |        5 |
|    2 |        0 |        6 |
|    3 |        1 |        9 |
|    4 |        1 |        8 |
|    5 |        0 |        5 |
|    6 |        0 |        8 |
|    8 |        0 |        6 |
|    9 |        0 |        4 |
|   10 |        1 |        5 |
|   11 |        0 |        2 |
|   12 |        0 |        3 |
+------+----------+----------+

Upvotes: 1

Views: 290

Answers (1)

Valerica
Valerica

Reputation: 1630

One solution could be this, i don't know if t is the best one:

select
    time
    ,col_1
    ,col_2
from @tbl
where col_1 = 1
union
select
    t2.time
    ,t2.col_1
    ,t2.col_2
from  @tbl t1
inner join @tbl t2
    on t1.time = t2.time-1
    or t1.time = t2.time-2
where t1.col_1 = 1
union
select
    t2.time
    ,t2.col_1
    ,t2.col_2
from  @tbl t1
inner join @tbl t2
    on t1.time = t2.time+1
    or t1.time = t2.time+2
where t1.col_1 = 1

Upvotes: 1

Related Questions