title: MySQL: Join distinct rows of two tables in a certain order?
body: <p>I have a list of inventory units and sale transactions that I want to, (1) join by unit SKU, and (2) associate ONE transaction to ONE inventory unit in first-in-first-out order by date. I'm having trouble with the second part.</p>
  
  <p>The best I can come up with is:</p>
  
  <pre><code>SELECT `units`.`unit_date`, `units`.`unit_id`, `trans`.`tran_date`, `trans`.`tran_id`, `units`.`unit_sku` FROM `units`
      INNER JOIN `trans`
          ON `trans`.`unit_sku` = `units`.`unit_sku`
  GROUP BY `trans`.`tran_id`, `trans`.`unit_sku`
  ORDER BY `units`.`unit_date` asc, `trans`.`tran_date` asc
  ;
  </code></pre>
  
  <p><code>units</code> table:</p>
  
  <pre><code>unit_date  | unit_id | unit_sku
  2015-06-01 | 1       | U1KLM
  2015-06-02 | 2       | U1KLM
  2015-06-03 | 3       | U2QRS
  2015-06-04 | 4       | U2QRS
  2015-06-05 | 5       | U1KLM
  </code></pre>
  
  <p><code>trans</code> table:</p>
  
  <pre><code>tran_date  | tran_id | unit_sku
  2015-06-11 | A       | U2QRS
  2015-06-12 | B       | U1KLM
  2015-06-13 | C       | U1KLM
  2015-06-14 | D       | U2QRS
  2015-06-15 | E       | U1KLM
  </code></pre>
  
  <p>The <strong>desired result</strong> is one <code>tran_id</code> to be joined to one <code>unit_id</code> of the <code>unit_sku</code> by earliest-to-latest order of <code>unit_date</code>:</p>
  
  <pre><code>unit_date  | unit_id | tran_date  | tran_id | unit_sku
  2015-06-01 | 1       | 2015-06-12 | B       | U1KLM
  2015-06-02 | 2       | 2015-06-13 | C       | U1KLM
  2015-06-03 | 3       | 2015-06-11 | A       | U2QRS
  2015-06-04 | 4       | 2015-06-14 | D       | U2QRS
  2015-06-05 | 5       | 2015-06-15 | E       | U1KLM
  </code></pre>
  
  <p>The <strong>query (undesired) result</strong> joins <code>tran_id</code> only to the <code>unit_id</code> of the earliest occurrence of <code>unit_sku</code>:</p>
  
  <pre><code>unit_date  | unit_id | tran_date  | tran_id | unit_sku
  2015-06-01 | 1       | 2015-06-12 | B       | U1KLM
  2015-06-01 | 1       | 2015-06-13 | C       | U1KLM
  2015-06-01 | 1       | 2015-06-15 | E       | U1KLM
  2015-06-03 | 3       | 2015-06-11 | A       | U2QRS
  2015-06-03 | 3       | 2015-06-14 | D       | U2QRS
  </code></pre>
  
  <p>Any ideas on how to do get the desired result? In this setup, only <code>unit_date</code> and <code>tran_date</code> are sortable; the rest are randomly generated.</p>
  
  <p>Repro script:</p>
  
  <pre><code>DROP TEMPORARY TABLE IF EXISTS `units`;
  DROP TEMPORARY TABLE IF EXISTS `trans`;
  CREATE TEMPORARY TABLE `units` (`unit_date` date, `unit_id` char(1) , `unit_sku` char(5), PRIMARY KEY(`unit_id`));
  CREATE TEMPORARY TABLE `trans` (`tran_date` date, `tran_id` char(1) , `unit_sku` char(5), PRIMARY KEY(`tran_id`));
  
  INSERT INTO `units` (`unit_date`, `unit_id`, `unit_sku`) VALUES
      ('2015-06-01', '1', 'U1KLM')
      , ('2015-06-02', '2', 'U1KLM')
      , ('2015-06-03', '3', 'U2QRS')
      , ('2015-06-04', '4', 'U2QRS')
      , ('2015-06-05', '5', 'U1KLM')
  ;
  
  INSERT INTO `trans` (`tran_date`, `tran_id`, `unit_sku`) VALUES
      ('2015-06-11', 'A', 'U2QRS')
      , ('2015-06-12', 'B', 'U1KLM')
      , ('2015-06-13', 'C', 'U1KLM')
      , ('2015-06-14', 'D', 'U2QRS')
      , ('2015-06-15', 'E', 'U1KLM')
  ;
  
  SELECT `units`.`unit_date`, `units`.`unit_id`, `trans`.`tran_date`, `trans`.`tran_id`, `units`.`unit_sku` FROM `units`
      INNER JOIN `trans`
          ON `trans`.`unit_sku` = `units`.`unit_sku`
  GROUP BY `trans`.`tran_id`, `trans`.`unit_sku`
  ORDER BY `units`.`unit_date` asc, `trans`.`tran_date` asc
  ;
  </code></pre>