Select On Value Change

I have a table that looks like this in a MySQL database: CREATE TABLE IF NOT EXISTS Example(Batch_Num int, Time DATETIME); INSERT INTO Example VALUES (1,'2020-12-04 05:06:12'), (

Solution 1:

This is a kind of gaps-and-islands problem. Islands are adjacent rows that have the same batchnum, and you want the start of each island.

Here, the simplest approach is probably lag():

select*from (
    select e.*,
        lag(batchnum) over(orderbytime) lag_batchnum
    from example e
) e
wherenot lag_batchnum <=> batchnum

Note that this requires MySQL 8.0. In earlier versions, one alternative uses a correlated subquery:

select e.*from example e
wherenot batchnum <=> (
    select e1.batchnum
    from example e1
    where e1.time < e.time
    orderby e1.time desc
    limit 1
)

Here is a demo on DB Fiddle.