title: MS-Access Select 1 row from GROUP BY query
body: <p>Always had a hard time wrapping my head around GROUP BY functionality, and this one is no exception.</p>
  <p>I have a simple Join query as such</p>
  <pre><code>Select t1.g1, t1.g2, t2.id, t2.datetime, t3.name 
  From ((table1 t1 Inner Join table2 t2 on t1.fld1=t2.fld1)
  Inner Join table3 t3 on t1.fld2=t3.fld2)
  Order By t2.datetime, t2.id
  </code></pre>
  <p>This returns my data as expected. Here are some sample rows that illustrate what I am trying to retrieve with Group By...</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>t1.g1</th>
  <th>t2.g2</th>
  <th>t2.id</th>
  <th>t2.datetime</th>
  <th>t3.name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>726</td>
  <td>4506</td>
  <td>32</td>
  <td>9/12/2021</td>
  <td>nameA</td>
  </tr>
  <tr>
  <td>726</td>
  <td>4506</td>
  <td>33</td>
  <td>9/12/2021</td>
  <td>nameB</td>
  </tr>
  <tr>
  <td>726</td>
  <td>4506</td>
  <td>30</td>
  <td>9/13/2021</td>
  <td>nameC</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to grab <strong>ONLY</strong> the first row in each Group of <strong>t1.g1, t1.g2</strong>.</p>
  <p>So, I try the following:</p>
  <pre><code>Select t1.g1, t1.g2, FIRST(t2.id), FIRST(t2.datetime), FIRST(t3.name) 
  From ((table1 t1 Inner Join table2 t2 on t1.fld1=t2.fld1)
  Inner Join table3 t3 on t1.fld2=t3.fld2)
  Group By t1.g1, t1.g2
  Order By FIRST(t2.datetime), FIRST(t2.id)
  </code></pre>
  <p>For the example Group above, this returns the following record...</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>t1.g1</th>
  <th>t2.g2</th>
  <th>t2.id</th>
  <th>t2.datetime</th>
  <th>t3.name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>726</td>
  <td>4506</td>
  <td>30</td>
  <td>9/13/2021</td>
  <td>nameC</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>So, <strong>Order By</strong> operates after the Grouping is done, not before. Or so it seems. Perhaps the reason for the order of the SQL keywords (Select, From, Where, Group By, Order By). Ok, makes sense if my assumption is correct. I think it finds t2.id=30 ahead of the other 726/4506 records because t2.id is a primary key on table2.</p>
  <p>So, now I try a nested Query, wherein my first query above returns the data in the correct order and the outside query groups and grabs the first record.</p>
  <pre><code>Select t1.g1, t1.g2, FIRST(t2.id), FIRST(t2.datetime), FIRST(t3.name) 
  FROM (
  Select t1.g1, t1.g2, t2.id, t2.datetime, t3.name 
  From ((table1 t1 Inner Join table2 t2 on t1.fld1=t2.fld1)
  Inner Join table3 t3 on t1.fld2=t3.fld2)
  Order By t2.datetime, t2.id
  )
  Group By t1.g1, t1.g2
  Order By FIRST(t2.datetime), FIRST(t2.id)
  </code></pre>
  <p>Same results! I am at a loss to understand how this is happening. So, if anyone can shed light on the order of functioning under-the-covers for Access SQL in this instance I would love to know. On my 2nd query (nested Select), it seems as though I am ordering the target data such that after Grouping the FIRST() aggregate function should select the first row found in the inner result set. But that is not happening.</p>
  <p>And of course, if anyone can tell me how to return the row I am after ...</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>t1.g1</th>
  <th>t2.g2</th>
  <th>t2.id</th>
  <th>t2.datetime</th>
  <th>t3.name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>726</td>
  <td>4506</td>
  <td>32</td>
  <td>9/12/2021</td>
  <td>nameA</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>That is all I really need.</p>