Find incorrect data from SQL

Question

As you can see below, data is increasing except 5th row. How can I find that data using SQL code?

id	value
1	102.312
2	103.412
3	104.621
4	105.512
5	102.561
6	106.632
7	107.742

question from:https://stackoverflow.com/questions/65840368/find-incorrect-data-from-sql

Answer 1

You need a column to represent the order you want to apply. Let's say there is a column id in your table we can use for this. Such a column is usually not guaranteed to be consecutive (i.e. without gaps).

The easiest way to find the rows where the value is not greater than the previous value is to use LAG which should be available in most modern DBMS. (For MySQL LAG is available as of version 8).

select *
from
(
  select t.*, lag(value) over (order by id) as previous_value from mytable t
) x
where value <= previous_value
order by id;