title: SQL self-join compare current record with the record of the previous date
body: <p>From the attached transaction table, the cardOldValue of each transaction should equal to the CardNewValue of the last transaction.</p>
  
  <p>I want to identify the TransactionID and the CardNumber of some suspicious cards who's CardOldValue is greater than the CardNewValue of the previous transaction. </p>
  
  <p>In the example, the transactionID '003' and cardNumber '1234' should be captured as it's greater than the previous transaction cardNewValue '50'.</p>
  
  <p><a href="https://i.stack.imgur.com/fQln7.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fQln7.jpg" alt="The transaction table"></a></p>
  
  <p>Following is what I have so far, but it doesn't give me the data I am after. Apparently I am not quite sure how self-join work:</p>
  
  <pre><code>SELECT 
      f1.TransactionID, f1.cardNumber, f1.cardOldValue, 
      f2.cardNewValue, f1.transactionDate, f2.transactionDate
  FROM 
      Transaction f1, Transaction F2
  WHERE 
      f2.transactionDate = (SELECT MAX(transactionDate) 
                            FROM Transaction
                            WHERE transactionDate &lt; f1.transactionDate
                              AND cardNumber = f2.cardNumber
                              AND f1.cardOldValue &gt; f2.cardNewValue)
  </code></pre>
  
  <p>I tested the sub-query separately, it gives me the date that is just older than the current date. So could you please let me know how to self-join the table properly?</p>