title: How to count only one row when there are duplicates with specific criteria?
body: <p>Let's say there are two tables Customer and Order:</p>
  <p>Customer:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CustomerID</th>
  <th>Name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Adam</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Terry</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Orders:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>OrderID</th>
  <th>CustomerID</th>
  <th>Status</th>
  <th>CreatedDate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>Cancelled</td>
  <td>8/30/2021 8AM</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>Cancelled</td>
  <td>8/30/2021 9AM</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>Pending</td>
  <td>8/30/2021 10AM</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2</td>
  <td>Cancelled</td>
  <td>8/22/2021 8AM</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2</td>
  <td>Pending</td>
  <td>8/31/2021 1PM</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>If an order is placed on the same day, I want to capture the row with the latest datetime or biggest OrderID. If an order is placed on different days, I want to capture the first datetime the order was placed, regardless of status. I have only been able to query each of these conditions separately, but not together. OrderID should be unique in this query.</p>
  <p>Expected query output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>OrderID</th>
  <th>CustomerID</th>
  <th>Status</th>
  <th>CreatedDate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>Pending</td>
  <td>8/30/2021 10AM</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2</td>
  <td>Cancelled</td>
  <td>8/22/2021 8AM</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Any help or advice would be greatly appreciated.</p>