title: SQL help for table variable/CTE
body: <p>Gurus,
  I have a table which has log of all customer's shopping details as below. shopmode is a master table id.</p>
  
  <pre><code>ShopDate    CustomerId  ShoppingMode
  1/1/2011    a                  0
  1/1/2011    a                  0
  1/1/2011    a                  1
  1/1/2011    b                  0
  2/1/2011    a                 0
  2/1/2011    b                 1
  3/1/2011    a                 1
  3/1/2011    b                 0
  3/1/2011    c                0
  </code></pre>
  
  <p>I am trying to comeup with query for requirements
  (date is in dd/mm/yyyy)</p>
  
  <ol>
  <li>Show one record per customer on shopdate,customerid,shopmode</li>
  </ol>
  
  <blockquote>
  <pre><code>1/1/2011  a  0
  1/1/2011  a  1
  1/1/2011  b  0
  </code></pre>
  </blockquote>
  
  <ol start="2">
  <li>on a given date range(1/1/2011- 3/1/2011), need most recent shop date having unique value of customerid + shopmode</li>
  </ol>
  
  <blockquote>
  <pre><code>3/1/2011  a    1
  3/1/2011  b    0
  3/1/2011  c    0
  2/1/2011  b    1
  2/1/2011  a    0
  </code></pre>
  </blockquote>
  
  <ol start="3">
  <li>on a given date range(1/1/2011-3/1/2011), the most recent by shopdate for customerid </li>
  </ol>
  
  <blockquote>
  <pre><code>3/1/2011   a   1    
  3/1/2011   b   0    
  3/1/2011   c   0
  </code></pre>
  </blockquote>
  
  <p>Need your help..</p>
  
  <pre><code>SELECT Max(shopdate),customerid, shopmode
  FROM Table 
  </code></pre>
  
  <p>with this result I will be joining shoppingdetail table to show data. Am tring to create a table variable or CTE show that I can join with other table.</p>