title: SQL query won't run when using Group By
body: <p>I'm trying to write an SQL query that displays the date of the most recent invoice sent to a client. Having read around, using a <code>MAX()</code> aggregate function and <code>GROUP BY</code> seems to be the way to go.</p>
  
  <p>However, my query is throwing up an error. I've tried grouping by just the function (as seen in the code below), and grouping by everything, but neither works.</p>
  
  <p>SQL is:</p>
  
  <pre><code>SELECT
      ClientSupplier.ClientID AS ClientID,
      ClientSupplier.ContactID AS ContactID,
      ClientSupplier.ClientCode AS ClientCode,
      Contact.Pref AS Pref,
      Contact.FName AS FName,
      Contact.LName AS LName,
      ContactPartnerHistory.PartnerID AS PartnerID,
      ContactPartnerHistory.EndDate AS EndDate,
      Partner.PartnerID AS PartnerID2,
      Partner.EmployeeID AS EmployeeID,
      Employee.EmployeeID AS EmployeeID2,
      Employee.PrefTemp AS PrefTemp,
      Employee.FNameTemp AS FNameTemp,
      Employee.SNameTemp AS SNameTemp,
      Bill.BillToClientID AS BillToClientID,
      Bill.InvoiceDate AS InvoiceDate,
      Max(Bill.InvoiceDate) AS MostRecentInvoiceDate
  FROM
      Bill 
  INNER JOIN 
      (ClientSupplier 
  INNER JOIN 
      (Contact 
  INNER JOIN 
      (ContactPartnerHistory 
  INNER JOIN 
      (Partner 
  INNER JOIN 
      Employee ON Partner.EmployeeID = Employee.EmployeeID) 
    ON Partner.PartnerID = ContactPartnerHistory.PartnerID) 
    ON Contact.ContactID = ContactPartnerHistory.ContactId) 
    ON Contact.ContactID = ClientSupplier.ContactID) 
    ON ClientSupplier.ClientID = Bill.BillToClientID
  WHERE
     ClientSupplier.ClientCode NOT LIKE '%zz%' 
     AND ContactPartnerHistory.EndDate IS NULL 
     AND Employee.SNameTemp LIKE '%payroll%'
  GROUP BY
     Max(Bill.InvoiceDate)
  </code></pre>
  
  <p>I've <a href="https://www.google.co.uk/search?q=is%20invalid%20because%20it%20is%20not%20contained%20in%20either%20an%20aggregate%20function&amp;rls=com.microsoft%3aen-GB&amp;ie=UTF-8&amp;oe=UTF-8&amp;startIndex=&amp;startPage=1&amp;safe=active&amp;gfe_rd=cr&amp;ei=6tKWU8_eMMPR8gfg5oGoBw&amp;gws_rd=ssl#q=is%20invalid%20because%20it%20is%20not%20contained%20in%20either%20an%20aggregate%20function%20site%3astackoverflow.com&amp;rls=com.microsoft%3aen-GB&amp;safe=active" rel="nofollow">checked the answers</a> on here regrading the error message I'm getting, but they seem to suggest that including everything in the group would clear it up, which it doesn't.</p>
  
  <p>The query runs if I exclude the function altogether (and shows all invoice dates), but that's not what I want. Obviously I'm doing something wrong, but what?</p>