title: How to get SQL row by max of one column, group by another column
body: <p>I need to read out users from an old database to use for statistics in a new system, but I do not have the original user table. However there is a statistics table with the sum of each year, where I also can find all needed user information. In addition this also only give me the users that were active, which is what I need.</p>
  
  <p>The table has the following relevant columns: (the statistics columns are not relevant here)</p>
  
  <ul>
  <li>UserID</li>
  <li>FirstName</li>
  <li>LastName</li>
  <li>Email</li>
  <li>Year</li>
  </ul>
  
  <p>I want UserID to be distinct, so it is the only column that I can have in GROUP BY.
  I will run MAX on Year, to get the values from the most recent year.
  FirstName, LastName and Email need to be the same as the row where MAX(Year). In other words people may have changed both names and emails during the years, and I only want the last one, as it's the only one that is relevant.</p>
  
  <p>My best suggestion for a SQL query goes like this:</p>
  
  <pre><code>SELECT UserID, Firstname, LastName, Email, MAX(Year) AS Year
  FROM myTable
  GROUP BY UserID
  ORDER BY LastName, FirstName
  </code></pre>
  
  <p>The only problem is that SQL Server 2008 will not let me do anyhing like that, because all columns either has to be with a function like MAX or a part of GROUP BY. The columns FirstName, LastName and Email can't be under GROUP BY because that will make too many records. It seems somehow to work to put MAX on all of them, but then I have no way of knowing which column the MAX function is actually working on. I do not know for sure that it will be a problem, but I don't have time to look through 100 000 rows to see if there actually is a problem.</p>
  
  <p>So in short I want the whole row of five columns where MAX works on only one column and GROUP BY on another. Does anybody have a good solution, or is it actually safe to use MAX on all non-grouping rows?</p>