title: Multiple left joins with afilter on max value of a linked table
body: <p>Here is my structure.</p>
  <pre><code>- LIC_LICENCE
      - LIC_ID
      - LIC_NUMERO
      - LIC_SAI_ID
  - SAI_SAISON
      - SAI_ID
      - SAI_LIBELLE
  </code></pre>
  <p>(i edited my question to simplify the issue at max)</p>
  <p>There are multiple LIC_LICENCE per LIC_NUMERO.
  Each LIC_LICENCE is linked to only one SAI_SAISON by the SAI_ID field.</p>
  <p>I want, for each LIC_NUMERO, to get the one LIC_LICENCE corresponding to the greater season ( max(SAI_LIBELLE) ).</p>
  <p>Every tries i did gave me all the LIC_LICENCE.</p>
  <p>Example of data :
  LIC_LICENCE</p>
  <ul>
  <li>LIC_ID / LIC_NUMERO / LIC_SAI_ID</li>
  <li>101    /   2001     /    9001</li>
  <li>102    /   2001     /    9002</li>
  <li>103    /   2001     /    9003</li>
  <li>104    /   2002     /    9003</li>
  <li>105    /   2002     /    9002</li>
  </ul>
  <p>SAI_SAISON</p>
  <ul>
  <li>SAI_ID  /  SAI_LIBELLE</li>
  <li>9001    /  &quot;2021/2022&quot;</li>
  <li>9002    /  &quot;2020/2021&quot;</li>
  <li>9003    /  &quot;2019/2020&quot;</li>
  </ul>
  <p>Result expected would be (LIC_ID / LIC_NUMERO / SAI_LIBELLE) :</p>
  <ul>
  <li>102 / 2001 / &quot;2021/2022&quot;</li>
  <li>105 / 2002 / &quot;2020/2021&quot;</li>
  </ul>
  <p>Edit :</p>
  <p>So... I was able to get a solution, but i'm not sure it is the most efficient :</p>
  <pre><code>SELECT DISTINCT L4.LIC_NUMERO, SS.SAI_LIBELLE \
  FROM LIC_LICENCE AS LL \
  CROSS APPLY (SELECT TOP 1 * FROM LIC_LICENCE AS L3 \
    LEFT JOIN SAI_SAISON AS S ON S.SAI_ID = L3.LIC_SAI_ID \
    WHERE L3.LIC_NUMERO = LL.LIC_NUMERO ORDER BY S.SAI_LIBELLE DESC ) AS L4 \
  INNER JOIN SAI_SAISON AS SS ON SS.SAI_ID = L4.LIC_SAI_ID \
  ORDER BY L4.LIC_NUMERO ;
  </code></pre>