title: In SQL remove duplicate groups
body: <p>In SQL Server, I have the below data set after a query. I need to group this data by uid then remove &quot;duplicates&quot; and return the grouping with the most recent column D. I also need to return the 2 most recent deduplicated groups. A group is defined as a duplicate if:</p>
  <ul>
  <li>it has the same number of rows</li>
  <li>Columns A, B, C are the same</li>
  <li>the order of the rows is the same</li>
  </ul>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">Uid</th>
  <th style="text-align: center;">A</th>
  <th style="text-align: center;">B</th>
  <th style="text-align: center;">C</th>
  <th style="text-align: center;">D1</th>
  <th style="text-align: center;">D2</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2021-02-19</td>
  <td style="text-align: center;">2021-02-19 09:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2021-02-19</td>
  <td style="text-align: center;">2021-02-19 10:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2021-02-19</td>
  <td style="text-align: center;">2021-02-19 11:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2021-01-19</td>
  <td style="text-align: center;">2021-01-19 09:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2021-01-19</td>
  <td style="text-align: center;">2021-01-19 10:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2020-02-19</td>
  <td style="text-align: center;">2020-02-19 09:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2020-02-19</td>
  <td style="text-align: center;">2020-02-19 10:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2020-02-19</td>
  <td style="text-align: center;">2020-02-19 11:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">11</td>
  <td style="text-align: center;">4</td>
  <td style="text-align: center;">5</td>
  <td style="text-align: center;">2000-10-05</td>
  <td style="text-align: center;">2000-10-05 09:00:00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>For example, in the above data set, uid's 1 and 3 are duplicates with 1 being the most recent. Hence the above data set should return</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">Uid</th>
  <th style="text-align: center;">A</th>
  <th style="text-align: center;">B</th>
  <th style="text-align: center;">C</th>
  <th style="text-align: center;">D1</th>
  <th style="text-align: center;">D2</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2021-02-19</td>
  <td style="text-align: center;">2021-02-19 09:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2021-02-19</td>
  <td style="text-align: center;">2021-02-19 10:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2021-02-19</td>
  <td style="text-align: center;">2021-02-19 11:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2021-01-19</td>
  <td style="text-align: center;">2021-01-19 09:00:00</td>
  </tr>
  <tr>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">6</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2021-01-19</td>
  <td style="text-align: center;">2021-01-19 10:00:00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I've tried the following window function:</p>
  <pre><code>FROM (
  
    SELECT 
      A,
      B,
      C,
      D1,
      D2,
      ROW_NUMBER() over (partition by 
                                      Uid                                    
                                      ORDER BY D2 DESC) as rn
  ....
  
  where rn = 1
  </code></pre>
  <p>but that does not allow me to group by uid. How can I accomplish this?</p>