CODEWITHSUNDEEP
phpmysqlcountrow
user2962317
user2962317

Reputation: 13

Get the amount of consecutive values from the end of the table

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

Upvotes: 1

Views: 93

Answers (4)

Dan
Dan

Reputation: 3103

I've changed the query. This should do the trick 

 SELECT count(`color`) FROM `table_name` GROUP BY `color` ORDER BY `id` DESC Limit 0,1

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

This seems to be a valid case for User Defined Variables. This solution will work regardless of whether the ID column has consecutive values or not:

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

See it working here.

Upvotes: 2

Jacob
Jacob

Reputation: 920

This will give you the result you are looking for 

SELECT COUNT(color) FROM table GROUP BY color ORDER BY id DESC LIMIT 1;

Upvotes: 0

user3015897
user3015897

Reputation: 1

do you finish it in mysql or php?

i don't konw mysql; but php you can do like this:

fetch all the result through the sql "SELECT * FROM table ORDER BY ID DESC ":

$count=0;$currentcolor='';

$row=mysql_fetch_assoc($result);

$currentcolor=$row['color'];

while($row=mysql_fetch_assoc($result)){
   if($row['color']==$currentcolor){
       $count++;
   }else{
      break;
   }
}

Upvotes: 0

Related Questions