title: MAX() as a filtering clause for a LEFT JOIN, Are there any alternatives?
body: <p>It seems to me that there is another option for this query, but I cannot pinpoint exactly what is that option:</p>
  <pre><code>@Query(&quot;&quot; +
          &quot;SELECT &quot; +
          &quot;... not really important... &quot; +
          &quot;FROM (&quot; +
          &quot;SELECT ect.* &quot; +
          &quot;FROM element_constant_table ect &quot; +
          &quot;WHERE book_parent_id = :bookId AND availability = 1&quot; +
          &quot;) ect1 &quot; +
          &quot;LEFT JOIN &quot; +
          &quot;element_version_table evt &quot; +
          &quot;ON (&quot; +
              &quot;SELECT evt.element_parent_id &quot; +
              &quot;WHERE &quot; +
              &quot;evt.time = (&quot; +
                  &quot;SELECT MAX(evt2.time) &quot; +
                  &quot;FROM element_version_table evt2 &quot; +
                  &quot;WHERE evt2.element_parent_id = evt.element_parent_id&quot; +
              &quot;) &quot; +
          &quot;) = ect1.element_id&quot; +
          &quot;&quot;)
  public abstract LiveData&lt;List&lt;Element&gt;&gt; getLatestElementsAt(int bookId);
  </code></pre>
  <p>For readability:</p>
  <pre><code>        SELECT 
          ... not really important... 
          FROM (
          SELECT ect.* 
          FROM element_constant_table ect 
          WHERE book_parent_id = :bookId AND availability = 1
          ) ect1 
          LEFT JOIN 
          element_version_table evt 
          ON (
              SELECT evt.element_parent_id 
              WHERE 
              evt.time = (
                  SELECT MAX(evt2.time) 
                  FROM element_version_table evt2 
                  WHERE evt2.element_parent_id = evt.element_parent_id
              ) 
          ) = ect1.element_id
  </code></pre>
  <p>The MAX() function is forcing a new instantiation of the whole <code>element_version_table</code> for each row found on <code>element_constant_table</code>.</p>
  <p>I was aware that something like this may happen so I always try to filter the table before it reaches the LEFT JOIN, in this case with the clause <code>&quot;WHERE book_parent_id = :bookId AND availability = 1&quot;</code></p>
  <p>There is no way to filter the LEFT TABLE and make it coincide with its corresponding key on the RIGHT TABLE before the ON clause, and so the WHERE needs to be placed there.</p>
  <p>The problem is that if instead of using the MAX(), I use a simple ORDER BY evt.time DESC LIMIT 1 inside the ON clause the compiler tells me that there is no evt.time field (I assume that it does not exist <strong>YET</strong>).
  So my conclusions to this are:</p>
  <p><strong>a) An ORDER BY requires for the table to be instantiated to its full length (It does not happens during the process of row location).</strong></p>
  <p><strong>b) The ON clause happens BEFORE the LEFT TABLE reaches instantiation (what I assume is) to it's full length.</strong></p>
  <p>Are my conclusions about the order of clause execution correct?</p>
  <p>How much of a burden may this query be on an extensive Database? and
  What would a better option be?</p>