title: Subtracting current from previous row by date and ID
body: <p>I'd like to extend this answer <a href="https://stackoverflow.com/questions/21244681/retrieve-previous-row-to-subtract-from-current-row-by-date#">here</a> by including an ID as a grouping column. I tried including a 'group by' clause without success.</p>
  
  <pre class="lang-none prettyprint-override"><code>+----------+--------------+----------
  |NAV_Date  |NetAssetValue |  ID      |
  +----------+--------------+---------+
  |12/31/2012|        $4,000|     A     |
  +----------+--------------+---------+
  |03/31/2013|        $5,000|     A     |
  +----------+--------------+---------+
  |12/31/2012|        $4,000|     B     |
  +----------+--------------+---------+
  |03/31/2013|        $5,000|     B     |
  +----------+--------------+---------+
  </code></pre>
  
  
  
  <pre><code>select NAV_date, NAV_value, (NAV_value / prev_value) - 1
  from (select t.*,
               (select top 1 NAV_value
                from YOURTABLENAMEGOESHERE as t2
                where t2.NAV_date &lt; t.NAV_date
                group by Nav_ID, Nav_value
                order by t2.NAV_date desc
               ) as prev_value
        from YOURTABLENAMEGOESHERE as t
       ) as t
  </code></pre>