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

Answers


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

Post Status

Asked in February 2016
Viewed 2,591 times
Voted 14
Answered 2 times

Search




Leave an answer