title: Return All Historical Account Records for Accounts with Change in Corresponding Value
body: <p>I am trying to select all records in a time-variant Account table for each account with a change in an associated value (e.g. the maturity date). A change in the value will result in the most recent record for an account being end-dated and a new record (containing a new effective date of the following day) being created. The most recent records for accounts in this table have an end-date of 12/31/9000.</p>
  
  <p>For instance, in the below illustration, account 44444444 would not be included in my query result set since it hasn't had a change in the value (and thus also has no additional records aside from the original); however, the other accounts have multiple changes in values (and multiple records), so I would want to see those returned.</p>
  
  <p><img src="https://i.stack.imgur.com/Vdnmw.png" alt="Illustration"></p>
  
  <p>I've tried using the row_num function, as well as a reflexive join, but for some reason I'm not getting the expected results. What are some ways to obtain the results I need? </p>
  
  <p>Note: The primary key for this table includes the acct_id and eff_dt. Also, I'm using PostgreSQL in a Greenplum environment.</p>
  
  <hr>
  
  <p>Here are two types of queries I tried to use but which produced problematic results:</p>
  
  <p><a href="https://i.stack.imgur.com/RPfsT.png" rel="nofollow noreferrer">Query 1</a><br>
  <a href="https://i.stack.imgur.com/BUc2y.png" rel="nofollow noreferrer">Query 2</a></p>