CODEWITHSUNDEEP
mysqlsql
kairos
kairos

Reputation: 522

MySQL get ids of the range between two conditions

Let's say I've a table

+----+------------+
| id | condition  |
+----+------------+
| 1  | open       |
+----+------------+
| 2  | content    |
+----+------------+
| 3  | content    |
+----+------------+
| 4  | close      |
+----+------------+
| 5  | nocontentx |
+----+------------+
| 6  | nocontenty |
+----+------------+
| 7  | open       |
+----+------------+
| 8  | content    |
+----+------------+
| 9  | close      |
+----+------------+
| 10 | nocontentz |
+----+------------+
| 11 | open       |
+----+------------+
| 12 | content    |
+----+------------+

and want to get a new table where I get the IDs (the first and the last) of the values between "close" and "open". Note that the values between this two conditions are dynamic (I can't search by "nocontent"whatever)

Such as I get this table:

+----+----------+--------+
| id | start_id | end_id |
+----+----------+--------+
| 1  | 5        | 6      |
+----+----------+--------+
| 2  | 10       | 10     |
+----+----------+--------+

Thanks in advance!

http://sqlfiddle.com/#!2/c255c8/2

Upvotes: 2

Views: 154

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You can do this using a correlated subquery:

select (@rn := @rn + 1) as id,
       id as startid,
       (select id
        from atable a2
        where a2.id > a.id and
              a2.condition = 'close'
        order by a2.id asc
        limit 1
       ) as end_id
from atable a cross join
     (select @rn := 0) vars
where a.condition = 'open';

The working SQL Fiddle is here.

Note this returns the third open as well. If you don't want it, then add having end_id is not null to the end of the query.

EDIT:

If you know the ids are sequential, you can just add and subtract 1 from the above query:

select (@rn := @rn + 1) as id,
       id+1 as startid,
       (select id
        from atable a2
        where a2.id > a.id and
              a2.condition = 'open'
        order by a2.id asc
        limit 1
       ) - 1 as end_id
from atable a cross join
     (select @rn := 0) vars
where a.condition = 'close';

You can also do this in a different way, which is by counting the number of open and closes before any given row and using this as a group identifier. The way your data is structured, every other group is what you are looking for:

select grp, min(id), max(id)
from (select t.*,
             (select sum(t2.condition in ('open', 'close'))
              from t t2
              where t2.id <= t.id
             ) as grp
      from t
     ) t
where t.condition not in ('open', 'close') and
      grp % 2 = 0
group by grp;

Upvotes: 5

Related Questions