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
4条答案
按热度按时间mdfafbf11#
You can do this with the self-JOIN and inequality conditional expressions:
4ioopgfo2#
You can use the MAX instead:
This checks so no previous IDs are higher than the current ID and outputs:
3duebb1j3#
When asking questions like this it is helpful to provide the DDL/DML. Using your example data:
Using this:
Here we're using a windowed function
MAX
to find the largest date ordered by theID
column. Using an outer query we can then compare theTimeStamp
to thisMAX
value and determine if the current row is less than the windowed maximum.q1qsirdb4#
Check for existence of an "earlier" row with a later date:
Your data would suggest that dates are unique but it's not clear you've described the most general problem.