title: sql how to extract the most common of values from a windowing function
body: <p>I have data that looks like</p>
  
  <pre><code>id   channel   amount    date
  a   PHONE       50     '1sep2014'
  a   PHONE      100     '1oct2014'
  a   INTERNET    20     '4oct2014'
  b   PHONE       25     '2oct2014'
  b   INTERNET    30     '3oct2014'
  c   INTERNET    35     '1oct2014'
  c   INTERNET    20     '2oct2014'
  c   INTERNET   100     '3oct2014'
  </code></pre>
  
  <p>I would like to mark out the most common channel per id in one step if possible.
  I know how do this using a group by and self joining, but whats the easiest to do it without using a self join?</p>
  
  <p>I already have a <code>row_number() OVER(PARTITION BY ID ORDER BY DATE DESC)</code> in my first query as I need to get the latest values/ channel etc from this table. I would like to tie it all into the same query if possible.</p>