Skip to content
Advertisement

Select on value change

I have a table that looks like this in a MySQL database:

I would like to select all lines where the Batch_Num is different from the previous value including the first one:

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():

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

Here is a demo on DB Fiddle.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement