Selecting records where autoincrement ID is bigger, but date column is in the past

Question

I have a table with two columns. First one is an autoincrement ID column, second one is a datetime column where next data should be in future to the previous. I need to select values where it is not true.

Example:

ID	TimeStamp
1	2022-10-10
2	2022-10-11
3	2022-10-12
4	2022-10-13
5	2022-08-01
6	2022-08-02
7	2022-08-03
8	2022-10-14
9	2022-10-15

I want to select only those rows, where previous dates is in future to them.

So from example above, my result should be rows 5,6,7.

I tried to do that with self-joins, and LAG/LEAD functions, but in that case I should know a quantity of this rows. In my case it can be any number of lines which don't fit into pattern

update.: table is very big(~5m records), so can't self join with '>'/'<' operators because it is not executing in time

Answer 1

Check for existence of an "earlier" row with a later date:

select * from T t1
where exists (
    select 1 from T t2
    where t2.id < t1.id and t2."timestamp" > t1."timestamp"
);

Your data would suggest that dates are unique but it's not clear you've described the most general problem.

Answer 2

You can use the MAX instead:

select *
from (
    select *, max(timestamp) over(order by id ROWS UNBOUNDED PRECEDING) as maxVal
    from (
        VALUES  (1, N'2022-10-10')
        ,   (2, N'2022-10-11')
        ,   (3, N'2022-10-12')
        ,   (4, N'2022-10-13')
        ,   (5, N'2022-08-01')
        ,   (6, N'2022-08-02')
        ,   (7, N'2022-08-03')
        ,   (8, N'2022-10-14')
        ,   (9, N'2022-10-15')
    ) t (ID,TimeStamp)
) x
where x.maxVal > x.TimeStamp

This checks so no previous IDs are higher than the current ID and outputs:

ID	TimeStamp	maxVal
5	2022-08-01	2022-10-13
6	2022-08-02	2022-10-13
7	2022-08-03	2022-10-13

Answer 3

When asking questions like this it is helpful to provide the DDL/DML. Using your example data:

DECLARE @Table TABLE (ID INT IDENTITY, TimeStamp DATE);

INSERT INTO @Table (TimeStamp) VALUES
('2022-10-10'), ('2022-10-11'), ('2022-10-12'), ('2022-10-13'),
('2022-08-01'), ('2022-08-02'), ('2022-08-03'), ('2022-10-14'),
('2022-10-15');

Using this:

SELECT *
  FROM (
        SELECT ID, TimeStamp, MAX(TimeStamp) OVER (ORDER BY ID) AS mTimeStamp
          FROM @Table
       ) a
 WHERE TimeStamp < mTimeStamp;

ID	TimeStamp	mTimeStamp
5	2022-08-01	2022-10-13
6	2022-08-02	2022-10-13
7	2022-08-03	2022-10-13

Here we're using a windowed function MAX to find the largest date ordered by the ID column. Using an outer query we can then compare the TimeStamp to this MAX value and determine if the current row is less than the windowed maximum.

Answer 4

You can do this with the self-JOIN and inequality conditional expressions:

SELECT DISTINCT t0.*
FROM [table] t0
INNER JOIN [table] t1 ON t1.ID < t0.ID AND t1.TimeStamp > t0.TimeStamp