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'), (1,'2020-12-04 05:06:13'), (1,'2020-12-04 05:06:14'), (2,'2020-12-04 05:06:20'), (2,'2020-12-04 05:07:12'), (2,'2020-12-04 05:07:20'), (1,'2020-12-04 05:07:25'), (1,'2020-12-04 05:07:35'), (3,'2020-12-04 05:07:35');
I would like to select all lines where the Batch_Num
is different from the previous value including the first one:
+----------+-----------------------+ | BatchNum | Change_Time | +----------+-----------------------+ | 1 | '2020-12-04 05:06:12' | | 2 | '2020-12-04 05:06:20' | | 1 | '2020-12-04 05:07:25' | | 3 | '2020-12-04 05:07:35' | +----------+-----------------------+
Is there a keyword maybe to access the previous line to compare the to the current line? Or some other way to compare a line to the line before it?
Advertisement
Answer
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(order by time) lag_batchnum from example e ) e where not lag_batchnum <=> batchnum
Note that this requires MySQL 8.0. In earlier versions, one alternative uses a correlated subquery:
select e.* from example e where not batchnum <=> ( select e1.batchnum from example e1 where e1.time < e.time order by e1.time desc limit 1 )
Here is a demo on DB Fiddle.