title: How to rank with multiple paritions in DB2?
body: <p>Hi if I have the following table</p>
  
  <pre><code>id    code     year
  --    ----     ----
  1     a        2000
  1     b        2001
  1     b        2002
  1     b        2003
  1     c        2007
  2     a        1997
  3     f        2006
  3     f        2014
  3     a        2015
  </code></pre>
  
  <p>I want to be able to rank the rows by partitioning on <code>id</code> and <code>code</code> while ordering by <code>year</code>:</p>
  
  <pre><code>id    code     year     ranked
  --    ----     ----     ------
  1     a        2000     1 
  1     b        2001     2
  1     b        2002     2
  1     b        2003     2
  1     c        2007     3
  2     a        1997     1
  3     f        2006     1
  3     f        2014     1
  3     a        2015     2
  </code></pre>
  
  <p>I have tried doing:</p>
  
  <pre><code>select distinct id, code, dense_rank() over(partition by id,code order by year) as ranked
  </code></pre>
  
  <p>however this has resulted in</p>
  
  <pre><code>id    code     year     ranked
  --    ----     ----     ------
  1     a        2000     1 
  1     b        2001     2
  1     b        2002     3
  1     b        2003     4
  1     c        2007     5
  2     a        1997     1
  3     f        2006     1
  3     f        2014     2
  3     a        2015     3
  </code></pre>
  
  <p>i.e. it appears to have only partitioned on <code>id</code></p>
  
  <p>I have tried <code>rank()</code> and <code>row_number()</code> too but these do not give the desired table. Am I missing something? I am sure dense_rank is what I need.</p>