title: What's an efficient way to find rows where the timestamp and identity are not in sequence?
body: <p><strong>Background:</strong></p>
  
  <p>I have a MS SQL application that reads data from our Oracle billing database once an hour, looking for new payments.  It does this by storing a timestamp based on the <code>CRT_DTTM</code> of the most recent timestamp found each time it runs.  </p>
  
  <p><em>e.g.</em></p>
  
  <pre><code>SELECT *
  FROM V_TRANS
  WHERE TRANS_CLS = 'P'
  AND CRT_DTTM &gt; TO_DATE('2010-01-25 12:59:44', 'YYYY-MM-DD HH24-MI-SS')
  </code></pre>
  
  <p>After this result is returned, the <code>MAX(CRT_DTTM)</code> is stored as the starting timestamp for the next hourly run.</p>
  
  <p>What appears to be happening is that sometimes there's a transaction running on the Oracle side that is inserting data into the table at the same time I'm running my query.  The rows are seemingly not being inserted in order of the timestamp they got.  This means that my <code>MAX(CRT_DTTM)</code> is greater than some of the rows that get inserted after my query completes.  I've missed payment information, and my systems are out of balance.</p>
  
  <p>I believe that I can work around this by simply modifying my SQL statement above to add:</p>
  
  <pre><code>...
  AND CRT_DTTM &lt; SYSDATE - INTERVAL '10' MINUTE
  </code></pre>
  
  <p><strong>The Question:</strong></p>
  
  <p>What I want to know is if there's a way to examine the rows already inserted into the table to find those pockets where the identity is out of order with the timestamp:</p>
  
  <p><a href="https://i.stack.imgur.com/yRbdd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yRbdd.png" alt="database sequence vs timestamp"></a>
  </p>
  
  <p>I want to find the pockets of data with this situation to know if 10 minutes is long enough to hold off on looking at the timestamp.</p>
  
  <pre><code>SELECT *
  FROM V_TRANS t1
  JOIN V_TRANS t2
      ON t1.trans_id &lt; t2.trans_id
          AND t2.crt_dttm &lt; t1.crt_dttm
  WHERE t1.TRANS_CLS = 'P'
      AND t1.CRT_DTTM &gt; TO_DATE('2010-01-25 12:59:44', 'YYYY-MM-DD HH24-MI-SS')
      -- Only look at an interval of one day
      AND t1.CRT_DTTM &lt; TO_DATE('2010-01-25 12:59:44', 'YYYY-MM-DD HH24-MI-SS') + 1
  </code></pre>
  
  <p>Or perhaps I'm overlooking some basic transaction isolation level setting?  I'm running this via a linked server with <code>OPENQUERY()</code></p>