title: SQL Earliest Record
body: <p>Let's say I have a table orders with 20 columns.  I'm only interested in the first 4 columns:  id, department_id, region_id, datetime where id is a customer id and datetime is the time the customer placed an order.  The other columns are more specific to product details (e.g. product_id), so on a given order, you may have multiple rows.  I'm struggling to write a query to get me the earliest department and region by each customer as the same customer can have multiple combinations of department_id and region_id.</p>
  
  <pre><code>SELECT a.*
  FROM (
      SELECT id,
          department_id,
          region_id,
          min(DATETIME) AS ts
      FROM orders
      GROUP BY id,
          department_id,
          region_id
      ) a
  INNER JOIN (
      SELECT id,
          min(DATETIME) AS ts
      FROM orders
      GROUP BY id
      ) b
      ON a.id = b.id
          AND a.ts = b.ts
  </code></pre>
  
  <p>This seems to work, but it doesn't seem very efficient and poorly written.  Is there a better way to write this?  The table itself is fairly large, so this query is slow.</p>