title: Aggregate Query and display highest value of non-aggregated columns
body: <p>how you doing?
  I've this question: Is it possible to aggregate a query with GroupBy on #col1 and then display the value of #col2 with the highest date?</p>
  <p>It's a problem that I've come accross multiple times and I've always got by using some logic in my code, but if it is possible it's nice to know.</p>
  <p>Let me give you a practical example:
  This query is pretty straightforward: It gets all the salary elements of a given person</p>
  <pre><code>SELECT dipElem.CodDip,
     dipElem.CodElemRetributivo as salElem,
     FORMAT(dipElem.DataInizioValidita, 'yyyy-MM-dd') AS datainizio,
     CONVERT(DECIMAL(10, 2), dipElem.ElemRetributivo) AS importo 
  FROM JBADipendElemRetrib dipElem
       INNER JOIN JBAElemRetr elemRetr ON elemRetr.CodElemRetributivo = 
  dipElem.CodElemRetributivo
  WHERE dipElem.CodDitta = :CodDitta
      AND elemRetr.CodContratto = :CodContratto
      AND dipElem.CodDipendente = :CodDipendente
      AND dipElem.DBGruppo = 'GG'
  ORDER BY dipElem.CodDipendente,
      dipElem.CodElemRetributivo ASC,
      dipElem.DataInizioValidita ASC;
  </code></pre>
  <p>And returns this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CodDip</th>
  <th>salElem</th>
  <th>datainizio</th>
  <th>importo</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>9</td>
  <td>1</td>
  <td>2019-11-04</td>
  <td>989.18</td>
  </tr>
  <tr>
  <td>9</td>
  <td>1</td>
  <td>2020-01-01</td>
  <td>989.18</td>
  </tr>
  <tr>
  <td>9</td>
  <td>1</td>
  <td>2020-11-01</td>
  <td>1201.14</td>
  </tr>
  <tr>
  <td>9</td>
  <td>1</td>
  <td>2021-11-01</td>
  <td>1314.19</td>
  </tr>
  <tr>
  <td>9</td>
  <td>2</td>
  <td>2019-11-04</td>
  <td>0.00</td>
  </tr>
  <tr>
  <td>9</td>
  <td>5</td>
  <td>2019-11-04</td>
  <td>0.00</td>
  </tr>
  <tr>
  <td>9</td>
  <td>6</td>
  <td>2019-11-04</td>
  <td>0.00</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>But, I want to return something like that:
  Grouped by salElem, with the <code>importo</code> of the highest <code>datainizio</code> possible for that <code>salElem</code>.</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>CodDip</th>
  <th>salElem</th>
  <th>datainizio</th>
  <th>importo</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>9</td>
  <td>1</td>
  <td>2021-11-01</td>
  <td>1314.19</td>
  </tr>
  <tr>
  <td>9</td>
  <td>2</td>
  <td>2019-11-04</td>
  <td>0.00</td>
  </tr>
  <tr>
  <td>9</td>
  <td>5</td>
  <td>2019-11-04</td>
  <td>0.00</td>
  </tr>
  <tr>
  <td>9</td>
  <td>6</td>
  <td>2019-11-04</td>
  <td>0.00</td>
  </tr>
  </tbody>
  </table>
  </div>