title: SQL: order by, then select first row with distinct value for multiple columns
body: <p>As a simplified example, I need to select each instance where a customer had a shipping address that was different from their previous shipping address.  So I have a large table with columns such as:</p>
  
  <pre><code>purchase_id | cust_id | date | address  | description
  -----------------------------------------------------------
   1          | 5       | jan  | address1 | desc1
   2          | 6       | jan  | address2 | desc2
   3          | 5       | feb  | address1 | desc3
   4          | 6       | feb  | address2 | desc4
   5          | 5       | mar  | address3 | desc5
   6          | 5       | mar  | address3 | desc6
   7          | 5       | apr  | address1 | desc7
   8          | 6       | may  | address4 | desc8
  </code></pre>
  
  <p>Note that customers can "move back" to a previous address as customer 5 did in row 7.</p>
  
  <p>What I want to select (and as efficiently as possible as this is a quite large table) is the first row out of every 'block' wherein a customer had subsequent orders shipped to the same address.  In this example that would be rows 1,2,5,7,and 8.  In all the others, the customer has the same address as their previous order.</p>
  
  <p>So effectively I want to first <code>ORDER BY (cust_id, date)</code>, then <code>SELECT purchase_id, cust_id, min(date), address, description</code>.</p>
  
  <p>However I'm having trouble because SQL usualy requires <code>GROUP BY</code> to be done before <code>ORDER BY</code>. I can't therefore figure out how to adapt e.g. either of the top answers to <a href="https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group">this question</a> (which I otherwise quite like.) It is necessary (conceptually, at least) to order by date before grouping or using aggregate functions like <code>min()</code>, otherwise I would miss instances like row 7 in my example table, where a customer 'moved back' to a previous address.</p>
  
  <p>Note also that two customers can share an address, so I need to effectively group by both <code>cust_id</code> and <code>address</code> after ordering by date.</p>
  
  <p>I'm using snowflake which I believe has most of the same commands available as recent versions of PostgreSQL and SQL Server (although I'm fairly new to snowflake so not completely sure.)</p>