Skip to main content

Mysql: Group Consecutive repeated row




event_id       event_name
----------------------------------------------
1                  Add
2                  Edit
2                  Edit
3                  Delete
3                  Delete
3                  Delete
1                  Add
1                  Add
1                  Add

Result
event_id       event_name  eve 
----------------------------------------------
1                  Add              1
2                  Edit              2
2                  Edit              2
3                  Delete          3
3                  Delete          3
3                  Delete          3
1                  Add              4
1                  Add              4
1                  Add              4
    
SELECT id,event_id,if(main.event_id = @eve,@eve,@row:=@row+1),@eve:=main.event_id,@row FROM `reso_event_log` as main,(select @row:=0,@eve:=0) as r order by main.id desc

Comments

Popular posts from this blog

Splitting Comma-Separated Values In MySQL

CREATE TABLE numbers (n INT);  INSERT INTO numbers VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),     select id, substring_index( substring_index(email_recipients, ',', n), ',', -1 ) as email from dashboards join numbers on char_length(email_recipients) - char_length(replace(email_recipients, ',', '')) >= n - 1     Reference Site : https://www.sisense.com/blog/splitting-comma-separated-values-in-mysql/

Custom Sorting Array List in PHP

 <?php $order = array("Z","Srilanka","34", "Canada", "India", "USA"); $array = array(     array('id' => 7867867, 'title' => 'USA'),     array('id' => 3452342, 'title' => 'India'),     array('id' => 1231233, 'title' => 'Srilanka'),     array('id' => 5867867, 'title' => 'Z'), ); usort($array, function ($a, $b) use ($order) {     $pos_a = array_search($a['title'], $order);     $pos_b = array_search($b['title'], $order);     return $pos_a - $pos_b; }); print_r($array); ?>