# Developers Planet

user3338991 February 2016

### SQL query select first from index

I am looking for a sql query to look for the following:

``````index A   B
1    5   1
2    10  1
3    15  0
4    10  0
5    20  1
6    5   0
7    15  1
8    25  0
9    20  0
10   15  0
``````

Selects "entry" row where B is 1, looks for 1st next row from that point downwards, the "exit" row, where A is either entry A value +10 and more, or -10 and more, doesn't matter the value of B in the "exit" row. return entry index, exit index, and some indicator if the exit was +10 or -10 would be great, if not possible, nwm. So in this case, the output of the query should be

``````entryindex exitindex +10/-10
1 3 +10    //entry in line 1, because B is 1, exit on line 15, because 15 is 5 +10
2 5 +10
5 6 -10
7 8 -10
``````

Kickstart February 2016

Quick attempt using a sub query:-

``````SELECT t1.index AS entryindex,
t2.index AS exitindex,
IF(t1.A <= t2.A, '+10', '-10') AS '+10/-10'
FROM
(
SELECT t1.index AS t1_index,
MIN(t2.index) AS t2_index
FROM a_table t1
INNER JOIN a_table t2
ON t1.index < t2.index
AND (t1.A <= t2.A - 10
OR t1.A >= t2.A - 10)
WHERE t1.B = 1
GROUP BY t2.index
) sub0
INNER JOIN a_table t1
ON t1.index = sub0.t1_index
INNER JOIN a_table t2
ON t2.index = sub0.t2_index
``````

Giorgos Betsos February 2016

You can use correlated subqueries to get expected result:

``````SELECT entryindex, exitindex, IF(entryA < exitA, '+10', '-10')
FROM (
SELECT t1.`index` AS entryindex,
(SELECT t2.`index`
FROM mytable AS t2
WHERE t2.`index` > t1.`index` AND
((t2.`A` >= t1.`A` + 10) OR (t2.`A` <= t1.`A` - 10))
ORDER BY `index` LIMIT 1) AS exitindex,
t1.`A` AS entryA,
(SELECT t2.`A`
FROM mytable AS t2
WHERE t2.`index` > t1.`index` AND
((t2.`A` >= t1.`A` + 10) OR (t2.`A` <= t1.`A` - 10))
ORDER BY `index` LIMIT 1) AS exitA
FROM mytable AS t1
WHERE t1.`B` = 1) AS t
ORDER BY entryindex
``````

Demo here