title: SQL Order by Timestamp and Assign Numeric Value
body: <p>I have a list of cust_id and timestamps for an activity.  I would like to add a column called "activity_order" which would give an order to each cust_id, which "1" being assigned to the max timestamp</p>
  
  <pre><code> cust_id | time_stamp
   ________  __________
   a1        2015-01-31 10:48:43
   a1        2015-01-31 12:48:46
   a1        2015-01-31 17:50:40
   b1        2015-01-25 10:39:01
   b1        2015-01-31 12:53:34
  </code></pre>
  
  <p>This is what I want my desired result to look like:</p>
  
  <pre><code> cust_id | time_stamp            | activity_order
   ________  ___________________    _________________
   a1        2015-01-31 10:48:43     3    
   a1        2015-01-31 12:48:46     2
   a1        2015-01-31 17:50:40     1
   b1        2015-01-25 10:39:01     2
   b1        2015-01-31 12:53:34     1
  </code></pre>
  
  <p>Here is my attempt, but the problem is this CASE statement is only so big, and I'm thinking I'll need a loop or something similar just in case a cust_id has greater than > 2 activities.</p>
  
  <pre><code>SELECT a.cust_id
  ,a.time_stamp
  ,CASE WHEN a.time_stamp = b.max_ts THEN 1 ELSE 2 END as activity_order
  FROM ACTIVITY a
  JOIN (SELECT
  cust_id
  ,MAX(time_stamp) as max_ts
  FROM activity_order) b
  ON a.cust_id = b.cust_id
  </code></pre>