title: MySQL Select distinct values across multiple tables
body: <p>I have two tables:</p>
  
  <pre><code>table_1
  uid | xid | name
  
  table_2
  uid | elements | time | pkey
  </code></pre>
  
  <p>I want to select multiple rows of xid, elements and time where time is the 10 smallest values and uid (or xid) is distinct.</p>
  
  <p>uid, xid in in table_1 is unique and the relationship between uid in table_1 and table_2 is one to many..</p>
  
  <p>I tried something like this but its not really working too well:</p>
  
  <pre><code>SELECT table_1.xid, MIN(table_2.time), table_2.elements
  FROM table_1, table_2
  WHERE table_1.uid= table_2.uid
  GROUP BY table_2.uid
  LIMIT 10
  </code></pre>
  
  <p>Lets take some data to play around with:</p>
  
  <pre><code>table_1
  uid | xid | name
  1 | 435 | John
  2 | 596 | Jane
  
  
  table_2
  uid | elements | time | pkey
  1 | 1 | 567 | 0
  2 | 2 | 335 | 1
  1 | 1 | 435 | 2
  1 | 2 | 456 | 3
  2 | 1 | 337 | 4
  1 | 1 | 428 | 5
  </code></pre>
  
  <p>How would I select the top 2 distinct results for each UID?
  In this case:</p>
  
  <pre><code>fid| elements | time
  596| 2 | 335
  435| 1 | 428
  </code></pre>
  
  <p>Thanks!!!</p>
  
  <p>In case people don't understand why lexu's solution does not work - it does not bind to primary key on table 2</p>
  
  <p>If I change above data to :</p>
  
  <pre><code>table_2
  uid | elements | time | pkey
  1 | 1 | 567 | 0
  2 | 2 | 335 | 1
  1 | 1 | 435 | 2
  1 | 2 | 456 | 3
  2 | 1 | 337 | 4
  1 | 2 | 428 | 5
  </code></pre>
  
  <p>Keep table_1 the same then the result should be:</p>
  
  <pre><code>fid| elements | time
  596| 2 | 335
  435| 2 | 428
  </code></pre>
  
  <p>but with @lexu's solution result is:</p>
  
  <pre><code>fid| elements | time
  596| 2 | 335
  435| 1 | 428
  </code></pre>
  
  <p>Nonetheless, thanks everyone for the help and especially @eagle!</p>