A bull can help explain the SQL

See in a post on stackoverflow:
I need help with this one, i have no idea how to make it.

Here is my data in MySql
----------------------
| id | color |
----------------------
| 1 | green |
| 2 | red |
| 3 | red |
| 4 | black |
| 5 | red |
| 6 | black |
| 7 | black |
----------------------

How to count the last data in color ? but not in all rows, I want to count only how much time is last data one after another. In this case result shuld be:

black is 2 times in a row

or
----------------------
| id | color |
----------------------
| 1 | green |
| 2 | red |
| 3 | red |
| 4 | black |
| 5 | red |
| 6 | black |
| 7 | black |
| 9 | green |
| 10 | red |
| 11 | red |
| 12 | red |
----------------------

and here will be:

red is 3 times in a row

Finally there is a bull to solve the following SQL

select max(amount) amount from (
select color,
@found := if(@found, true, @prev_color != color),
@prev_color := color,
@amount := @amount + (not @found) amount
from table, (
select @prev_color := (select color from table order by id desc limit 1),
@found := false,
@amount := 0) init
order by id desc
) s

I said that the SQL method had never seen, and great guiding explain very grateful

Started by Lyle at December 12, 2016 - 2:48 AM

It is easier to understand a little bit.
select count(*) from tb
where id >(
select max(id) from tb
where color<>(
select color from tb
where id=(select max(id) from tb)
)
)

Posted by Andra at December 15, 2016 - 3:37 AM