How to get SQL row by maximum of one column, group after another column

Question

I need to read users from the old database for use in statistics on the 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 can also find all the necessary user information. In addition, it also gives me only those users who were active, and this is what I need.

The table has the following corresponding columns: (statistics columns are not relevant here)

• User ID
• Firstname
• Lastname
• Email
• Year

I want the UserID to be different, so this is the only column I can have in GROUP BY. I will run MAX on Year to get values ​​from the very last year. FirstName, LastName, and Email must match the string MAX (Year). In other words, people could change names and emails over the years, and I only need the latter, as it is relevant.

My best suggestion for an SQL query is as follows:

SELECT UserID, Firstname, LastName, Email, MAX(Year) AS Year FROM myTable GROUP BY UserID ORDER BY LastName, FirstName 

The only problem is that SQL Server 2008 will not let me do anything like that, because all columns must be with the MAX function or the GROUP BY part. The FirstName, LastName, and Email columns cannot be under GROUP BY because this will make too many entries. Something seems to work to put MAX on all of them, but then I have no way of knowing which column the MAX function works in. I don’t know for sure that this will be a problem, but I don’t have time to look at 100,000 lines to see if the problem really is.

In short, I need a whole row of five columns, where MAX works with only one column, and GROUP BY with another. Does anyone have a good solution or is it safe to use MAX for all ungrouped rows?

Answer 1

A few answers ...

Correlated Subquery ...

 SELECT * FROM myTable WHERE Year = (SELECT MAX(Year) FROM myTable AS lookup WHERE lookup.UserID = myTable.UserID) 

Join the derived population ...

 SELECT * FROM myTable INNER JOIN (SELECT UserID, MAX(Year) AS Year FROM myTable GROUP BY UserID) AS lookup ON lookup.UserID = myTable.UserID AND lookup.Year = myTable.Year 

Ordered CTE using ROW_NUMBER () ...

 WITH sequenced_data AS ( SELECT ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY Year DESC) AS sequence_id, * FROM myTable ) SELECT * FROM sequenced_data WHERE sequence_id = 1 

Answer 2

Do you have only one year for each user? If so, then you can use old'n'good join:

 SELECT m.UserID, m.Firstname, m.LastName, m.Email, m.Year FROM myTable m INNER JOIN ( SELECT UserID, MAX(Year) as Year FROM myTable GROUP BY UserID ) x ON x.UserID=m.UserID and x.Year=m.Year ORDER BY m.LastName, m.FirstName 

Of course, you can use constructs from newer versions of SQL, I'm just used to the older (= more general) features :).