Dado que tenemos la siguiente tabla donde el número de serie y la fecha deben incrementarse
+----+--------+------------+ | id | series | date | +----+--------+------------+ | 1 | 10 | 2020-08-13 | | 2 | 9 | 2020-08-02 | | 3 | 8 | 2020-06-23 | | 4 | 7 | 2020-06-08 | | 5 | 6 | 2020-05-20 | | 6 | 5 | 2020-05-05 | | 7 | 4 | 2020-05-01 | +----+--------+------------+¿Hay alguna forma de verificar si hay registros que no siguen este patrón? Por ejemplo, la fila 2 tiene un número de serie más grande pero su fecha es anterior a la fila 3
+----+--------+------------+ | id | series | date | +----+--------+------------+ | 1 | 10 | 2020-08-13 | | 2 | 9 | 2020-06-02 | | 3 | 8 | 2020-07-23 | | 4 | 7 | 2020-06-08 | | 5 | 6 | 2020-05-20 | | 6 | 5 | 2020-05-05 | | 7 | 4 | 2020-05-01 | +----+--------+------------+Puede utilizar funciones de ventana:
select * from ( select t.*, lead(date) over(order by series) lead_date from mytable t ) t where date > lead_dateAlternativamente:
select * from ( select t.*, lead(series) over(order by date) lead_series from mytable t ) t where series > lead_seriesPuedes usar lag() :
select t.* from (select t.*, lag(id) over (order by series) as prev_id_series, lag(id) over (order by date) as prev_id_date from t ) t where prev_id_series <> prev_id_date;Puede obtener filas problemáticas y sus correspondientes filas en conflicto usando SELF JOIN de esta manera (suponiendo que su tabla se llame "serie"):
SELECT s1.id AS row_id, s1.series AS row_series, s1.date AS row_date, s2.id AS conflict_id, s2.series AS conflict_series, s2.date AS conflict_date FROM series AS s1 JOIN series AS s2 ON s1.series > s2.series AND s1.date < s2.date;