title: Select values from smallest value in table with a group by
body: <p>I have a table that stores all the distances for a user from different points (like startplace1 to workplace 1, startplace1 to workplace 2, startplace1 to workplace 3, startplace2 to workplace1,startplace2 to workplace1)</p>
  
  <pre><code>Every line has an 'ID', 'AID' (=memberid), 'FROM', 'TO', 'DISTANCE'
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/YxbnA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YxbnA.png" alt="Tablestructure"></a></p>
  
  <p>FYI: The fields 'from' &amp; 'to' are maybe the same codes, but it isn't the same place (stored in another table, just for example dom1 is home).</p>
  
  <p>I'm trying to <strong>select</strong> the <strong>smallest distance</strong> in a table <strong>for every 'aid'</strong> <code>(=memberid)</code> <strong>for every startpoint</strong> <em>(field from)</em> and some <strong>other fields</strong> related to the record with the smallest distance</p>
  
  <p>I am using codeigniter, and this is what I already have:</p>
  
  <pre><code>$this-&gt;db-&gt;SELECT('aid,from,to,min(distance) as mindistance');
  $this-&gt;db-&gt;FROM('distances');
  $this-&gt;db-&gt;GROUP_BY('aid');
  $this-&gt;db-&gt;GROUP_BY('from');
  </code></pre>
  
  <p>The query</p>
  
  <pre><code>SELECT `aid`, `from`, `to`, max(distance) as maxdistance FROM (`distances`) GROUP BY `aid`
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/nrDts.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nrDts.png" alt="Results without order by"></a></p>
  
  <p>This gives me the smallest distance, but the field 'to' isn't related to that record from the smallest distance. I tried also with ORDER_BY distance ASC but it's the same. </p>
  
  <p><a href="https://i.stack.imgur.com/VjqeB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VjqeB.png" alt="Results with order by"></a></p>
  
  <p>How can I select the field 'to' from the record with smallest distance, grouped by 'aid'-field and 'from'-field?</p>