title: Spark - iterative deduplication
body: <p>I have a below situation where i am stuck with framing the problem in spark sql. Below is the description.</p>
  <h2>Input</h2>
  <p>Suppose there are 3 columns in a spark df. (id_a , id_b, score_val ). Below is a sample data:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id_a</th>
  <th>id_b</th>
  <th>score_val</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1000</td>
  <td>5000</td>
  <td>10.5</td>
  </tr>
  <tr>
  <td>1000</td>
  <td>5001</td>
  <td>10.4</td>
  </tr>
  <tr>
  <td>2000</td>
  <td>5000</td>
  <td>10.9</td>
  </tr>
  <tr>
  <td>2000</td>
  <td>5001</td>
  <td>10.3</td>
  </tr>
  <tr>
  <td>3000</td>
  <td>5000</td>
  <td>11.1</td>
  </tr>
  <tr>
  <td>3000</td>
  <td>5001</td>
  <td>11.0</td>
  </tr>
  <tr>
  <td>3000</td>
  <td>5002</td>
  <td>10.8</td>
  </tr>
  </tbody>
  </table>
  </div><h2>Expected output</h2>
  <p>The ask is to deduplicate this dataset in a two way fashion. In this case, the output after deduplication should be as follows-</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id_a</th>
  <th>id_b</th>
  <th>score_val</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1000</td>
  <td>5000</td>
  <td>10.5</td>
  </tr>
  <tr>
  <td>2000</td>
  <td>5001</td>
  <td>10.3</td>
  </tr>
  <tr>
  <td>3000</td>
  <td>5002</td>
  <td>10.8</td>
  </tr>
  </tbody>
  </table>
  </div><h2>Explanation</h2>
  <ul>
  <li>for <code>id_a = 1000</code>, the program should pick out highest score_val record, in this case, the row with <code>id_b = 5000</code></li>
  <li>for <code>id_a = 2000</code>, the program should pick out row with <code>id_b = 5001</code>, since id_b=5000 has been previously mapped to <code>id_a = 1000</code></li>
  <li>for <code>id_a = 3000</code>, the program should pick out row with <code>id_b = 5002</code>, since <code>id_b = 5000</code> has been previously mapped to <code>id_a = 1000</code>, and
  <code>id_b = 5001</code> has been previously mapped to <code>id_a = 2000</code></li>
  </ul>
  <p>I have tried previously to use window function to traverse the rows and pick out the correct output.But i have been stuck and couldnt
  proceed. I have rewritten this to use MapPartitions feature of spark dataframe and implement a custom dedupe logic in core java.
  However its not optimal performance-wise.</p>
  <p>If someone has any clue how to solve this problem of simultaneous filter+deduplication operation using spark sql construct,
  it  will be greatly be appreciated!</p>