CODEWITHSUNDEEP
mysqlsql
Oscar Zarrus
Oscar Zarrus

Reputation: 790

Skip First consecutive records which has same values, then stop

This is my table scenario

+----------+------------+---------------------+------------------+
| userd_id | user_value |    user_datetime    | i_want_to_select |
+----------+------------+---------------------+------------------+
|        1 |          1 | 2020-01-31 12:13:14 |                  |
|        2 |          1 | 2020-01-30 12:13:14 |                  |
|        3 |          1 | 2020-01-29 12:13:14 |                  |
|        4 |          2 | 2020-01-28 12:13:14 |                  |
|        5 |          2 | 2020-01-27 12:13:14 |                  |
|        6 |          3 | 2020-01-20 12:13:14 |                  |
|        7 |          1 | 2020-01-19 12:13:14 | this             |
|        8 |          1 | 2020-01-18 12:13:14 | this             |
|        9 |          1 | 2020-01-17 12:13:14 | this             |
+----------+------------+---------------------+------------------+

The need is to skip those consecutive values, only IF first element is 1, ​​and stop as soon as the value is different

I do it in PHP code, but I want to avoid weighing down the PHP processor and the garbage collector

foreach ($rows as &$row){
   if($row['user_value'] === 1){
       unset($row); // remove my row
   } else {
      break; // at the first different value, stop
   }
}

unset($row);
$rows = array_values($rows) //reset the array index/key

Thanks

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

One method is:

select t.*
from t
where t.user_datetime >= (select min(t2.user_datetime)
                         from t t2
                         where t2.user_id = t.user_id and
                               t2.user_value <> 1
                        );

You can also use window functions, for instance:

select t.*
from (select t.*,
             min( case when t.user_value <> 1 then t user_datetime) over (partition by t.user_id) as min_not1_datetime
      from t
     ) t
where user_datetime >= min_not1_datetime;

Upvotes: 1

Related Questions