title: Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
body: <p>I'm trying to select the latest date and group by name and keep other columns.</p>
  
  <p>For example:</p>
  
  <pre><code>        name  status  date
          -----------------------
           a    l       13/19/04
           a    n       13/09/05
           a    dd      13/18/03
           b    l       13/01/01
           b    dd      13/01/02
           b    n       13/01/03
  </code></pre>
  
  <p>and I want the result like:</p>
  
  <pre><code>        name status date
          -----------------
            a    n      13/09/05
            b    n      13/01/03
  </code></pre>
  
  <p>Here's my code </p>
  
  <pre><code>SELECT
      Name,
      MAX(DATE) as Date,
      Status
  FROM
      [ST].[dbo].[PS_RC_STATUS_TBL] 
  GROUP BY 
      Name
  </code></pre>
  
  <p>I know that I should put max(status) because There are a lot of possibilities in each case, and nothing in the query makes it clear which value to choose for status in each group. Is there anyway to use inner join ?</p>