SQL query for checking if rows are in incorrect order-CodePudding

I have a table with this structure:

Id	Foo
1	12
2	13
3	14
4	15
5	16

Note that both tables are in an ascending order. However, due to reasons some values in column Foo are not in the correct order:

Id	Foo
1	12
2	13
3	15
4	14
5	16

Note that the Foo column is now no longer correctly ordered: Foo 14 and 15 are switched. I need a query that returns the ID 3 or 4, so that I know which rows are not correctly ordered and can manually inspect them. There are no gaps in the number series of either column.

CodePudding user response：

From what you describe a simple exists correlation should give your desired result:

select Id
from t
where exists (
  select * from t t2
  where t2.id = t.id   1 and t2.foo < t.Foo
);

Example Fiddle

CodePudding user response：

Another option is using LEAD or LAG

SELECT *
FROM (
    SELECT *,
      nextFoo = LEAD(t.Foo) OVER (ORDER BY t.ID)
    FROM t
) t
WHERE t.Foo > t.nextFoo;

db<>fiddle