Fetch results in group based on the occurrence

Question

Ok, I have a single MySQL table with the name 'car' and 3 columns.

+----+--------+------------+
| ID | car_id  | engine |
+----+--------+------------+
| 1  |  123     | on        |
| 2  | 123      | on        |
| 3  | 123      | off       |
| 4  | 123      | on        |
| 5  | 123      | on        |
| 6  | 123      | on        |
| 7  | 123      | off       |
| 8  | 123      | on        |
| 9  | 123      | off       |
+----+--------+------------+

Now I want to show the trips this car did. The trips would be determined based on car engine start and stop. For example from the above example we can see that user has made 3 trips as total(From on to off). Now What I want is that if there is a query which gives me only 3 results from on to off meaning if somehow the query groups the records by considering a starting point on and ending point off. Is it possible in mysql? or the other way around is doing it manually by fetching all the records and working in arrays?

At the moment I am fetching all the records and doing it manually by looping all the data and doing accordingly But this process is slow.

Answer 1

Can you try it ?

SELECT * from cars WHERE `engine` = 'off' AND id IN(SELECT id+1 FROM `cars` WHERE `engine` = 'on')