Count consecutive rows in mysql I would like to get the count of consecutive rows in MySQL. It is a large database I try to avoiding the joins. Here gadget_id means vehicle and every 20/30 second the vehicle sends the current location. I want to know how long time a vehicle halts in certain location. 

Will explain by below table, it is the latest data

id gadget_id  location  submitted_date  
   ---------  --------  ----------  
1  1          calicut   2012-07-15  
2  1          calicut   2012-07-14  
3  1          calicut   2012-07-13  
4  2          thrissur  2012-07-12  
5  1          calicut   2012-07-11
6  1          kannur    2012-07-10
7  2          thrissur  2012-07-09
8  1          calicut   2012-07-08    
…          …         …         


*I want to know how long time gadget_id 1 halts in position calicut.* 

Here we can show that the gadget_id = 1 and position=calicut last 4 data's comes from same position calicut. The next data of the gadget_id=1 is from kannur, so we avoid the data from this id. How to get the count 4 when we give the input gadget_id = 1 and position=calicut 

Anybody give the suitable query, expect a single query without joins.
Since gadget_id is a vehicle, you need to monitor two things as you look at each row

when a gadget_id switches location
when a gadget_id switches to another gadget_id

The solution lies in organizing a set of user variables to monitor that change. Please forgive you are about to see:
First, let's load your data in the test database in a table called gadget_location:
mysql&gt; use test
Database changed
mysql&gt; drop table gadget_location;
Query OK, 0 rows affected (0.07 sec)

mysql&gt; create table gadget_location
    -&gt; (
    -&gt;     id int not null auto_increment,
    -&gt;     gadget_id int,
    -&gt;     location  varchar(30),
    -&gt;     submitted_date date,
    -&gt;     primary key (id)
    -&gt; );
Query OK, 0 rows affected (0.13 sec)

mysql&gt; insert into gadget_location (gadget_id,location,submitted_date) values
    -&gt; (1,'calicut' , '2012-07-15'), (1,'calicut' , '2012-07-14'),
    -&gt; (1,'calicut' , '2012-07-13'), (2,'thrissur', '2012-07-12'),
    -&gt; (1,'calicut' , '2012-07-11'), (1,'kannur'  , '2012-07-10'),
    -&gt; (2,'thrissur', '2012-07-09'), (1,'calicut' , '2012-07-08');
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql&gt; select * from gadget_location;
+----+-----------+----------+----------------+
| id | gadget_id | location | submitted_date |
+----+-----------+----------+----------------+
|  1 |         1 | calicut  | 2012-07-15     |
|  2 |         1 | calicut  | 2012-07-14     |
|  3 |         1 | calicut  | 2012-07-13     |
|  4 |         2 | thrissur | 2012-07-12     |
|  5 |         1 | calicut  | 2012-07-11     |
|  6 |         1 | kannur   | 2012-07-10     |
|  7 |         2 | thrissur | 2012-07-09     |
|  8 |         1 | calicut  | 2012-07-08     |
+----+-----------+----------+----------------+
8 rows in set (0.00 sec)

mysql&gt;

OK, here comes the mess:
SET @dupcount = 0;
SET @group_number = 0;
SET @cur_gadget_id = 0;
SET @cur_location = MD5(&quot;1&quot;);
SET @cur_gadget_location = MD5(&quot;1&quot;);
SELECT gadget_id,location,GroupNumber,COUNT(1) DaysStopped FROM
(
SELECT
    *,
    @dupcount     := IF(@cur_gadget_location=gadget_location,@dupcount+1,1) Dup_Count,
    @group_number := IF(@cur_gadget_location=gadget_location,@group_number,@group_number+1) GroupNumber,
    @cur_gadget_location := gadget_location
FROM
(
    SELECT *,CONCAT(gadget_id,'-',cur_loc) gadget_location FROM
    (
        SELECT *,
        @cur_location  := IF(MD5(location)=@cur_location,@cur_location,MD5(location)) cur_loc,
        @cur_gadget_id := IF(gadget_id=@cur_gadget_id,@cur_gadget_id,gadget_id) cur_gadget
        FROM gadget_location
    ) AAA
) AA ) A GROUP BY gadget_id,location,GroupNumber;

Want to see it work ??? Here it goes:
mysql&gt; SET @dupcount = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @group_number = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @cur_gadget_id = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @cur_location = MD5(&quot;1&quot;);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SET @cur_gadget_location = MD5(&quot;1&quot;);
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SELECT gadget_id,location,GroupNumber,COUNT(1) DaysStopped FROM
    -&gt; (
    -&gt; SELECT
    -&gt;     *,
    -&gt;     @dupcount     := IF(@cur_gadget_location=gadget_location,@dupcount+1,1) Dup_Count,
    -&gt;     @group_number := IF(@cur_gadget_location=gadget_location,@group_number,@group_number+1) GroupNumber,
    -&gt;     @cur_gadget_location := gadget_location
    -&gt; FROM
    -&gt; (
    -&gt;     SELECT *,CONCAT(gadget_id,'-',cur_loc) gadget_location FROM
    -&gt;     (
    -&gt;         SELECT *,
    -&gt;         @cur_location  := IF(MD5(location)=@cur_location,@cur_location,MD5(location)) cur_loc,
    -&gt;         @cur_gadget_id := IF(gadget_id=@cur_gadget_id,@cur_gadget_id,gadget_id) cur_gadget
    -&gt;         FROM gadget_location
    -&gt;     ) AAA
    -&gt; ) AA ) A GROUP BY gadget_id,location,GroupNumber;
+-----------+----------+-------------+-------------+
| gadget_id | location | GroupNumber | DaysStopped |
+-----------+----------+-------------+-------------+
|         1 | calicut  |           1 |           3 |
|         1 | calicut  |           3 |           1 |
|         1 | calicut  |           6 |           1 |
|         1 | kannur   |           4 |           1 |
|         2 | thrissur |           2 |           1 |
|         2 | thrissur |           5 |           1 |
+-----------+----------+-------------+-------------+
6 rows in set (0.02 sec)

mysql&gt;

According to this output, here is what you have:
Gadget 1

Group 1 : stopped at calicut for 3 days
Group 3 : left and came back for 1 day
Group 4 : left calicut and went to kannur for 1 day
Group 6 : left kannur and went to calicut for 1 day

Gadget 2

Group 2 : stopped for thrissur for 1 day
Group 5 : left and came back for 1 day

For some reason, the GroupNumbers came out different in SQLFiddle`. Notwithstanding, the rest of the output is the same.
I hope this is right ...