title: SQL Query to get one row per group based on conditional logic
body: <p>I have a table which has duplicate values in one of the columns. I am trying to eliminate rows with duplicate entries based on some logic described below.</p>
  
  <p>Here is the table example -</p>
  
  <pre><code>RecoveryKey   DateTime      Duration CallDisposition NewTransaction Variable8   CallTypeID
  7994113912466 12/4/18 16:26 19       52              Y              152643-5657     -1    
  7994113912470 12/4/18 16:26 1168     29              Y              152643-5657   6390
  7994113912751 12/4/18 16:51 2686     13              N              152643-5657   6390
  7994113912756 12/4/18 13:51 56       2               Y              152643-5658   6390
  7994113912756 12/4/18 13:52 125      13              Y              152643-5658   6390
  7994113912756 12/4/18 13:55 125      13              N              152643-5658   6390
  </code></pre>
  
  <p>The column <code>Variable8</code> has duplicate values for the first 3 records and for the last 3 records.</p>
  
  <p>What do I want to do is, if <code>CallDisposition</code> is 29 and the next entry after that has a <code>NewTransaction</code> value of "N" then I have to keep the row with <code>CallDisposition</code> = 29.</p>
  
  <p>For all other instance I want to select latest row irrespective of <code>CallDisposition</code> or <code>NewTransaction</code> value.</p>
  
  <p>So my final result should look like this</p>
  
  <pre><code>RecoveryKey   DateTime      Duration CallDisposition NewTransaction Variable8   CallTypeID
  7994113912470 12/4/18 16:26 1168     29              Y              152643-5657   6390
  7994113912756 12/4/18 13:55 125      13              N              152643-5658   6390
  </code></pre>