Select rows until condition met

Question

I would like to write an Oracle query which returns a specific set of information. Using the table below, if given an id, it will return the id and value of B. Also, if B=T, it will return the next row as well. If that next row has a B=T, it will return that, and so on until a F is encountered.

So, given 3 it would just return one row: (3,F). Given 4 it would return 3 rows: ((4,T),(5,T),(6,F))

id B
1 F
2 F
3 F
4 T
5 T
6 F
7 T
8 F

Thank you in advance!

Answer 1

Use a sub-query to find out at what point you should stop, then return all row from your starting point to the calculated stop point.

SELECT
  *
FROM
  yourTable
WHERE
      id >= 4
  AND id <= (SELECT MIN(id) FROM yourTable WHERE b = 'F' AND id >= 4)

Note, this assumes that the last record is always an 'F'. You can deal with the last record being a 'T' using a COALESCE.

SELECT
  *
FROM
  yourTable
WHERE
      id >= 4
  AND id <= COALESCE(
              (SELECT MIN(id) FROM yourTable WHERE b = 'F' AND id >= 4),
              (SELECT MAX(id) FROM yourTable                          )
            )