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

jq6vz3qz  于 11个月前  发布在  其他
关注(0)|答案(4)|浏览(101)

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

mdfafbf1

mdfafbf11#

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
4ioopgfo

4ioopgfo2#

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:

IDTimeStampmaxVal
52022-08-012022-10-13
62022-08-022022-10-13
72022-08-032022-10-13
3duebb1j

3duebb1j3#

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;
IDTimeStampmTimeStamp
52022-08-012022-10-13
62022-08-022022-10-13
72022-08-032022-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.

q1qsirdb

q1qsirdb4#

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.

相关问题