Get row that has a Max value for a column in SQL Server

Question

I found a question that was very similar to this, but using features that seem to be exclusive to Oracle. I want to do this in SQL Server.

I have a table like this:

MyTable -------------------- MyTableID INT PK UserID INT Counter INT 

Each user can have several lines with different values ​​for Counter in each line. I need to find the rows with the highest Counter value for each user.

How to do this in SQL Server 2005?

The best I can find is a query that returns MAX(Counter) for each UserID , but I need the whole row due to other data in this table that is not shown in my table definition for simplicity.

EDIT:. It occurred to me some of the answers in this post that I forgot about an important detail. It is possible to have 2+ lines where UserID can have the same MAX counter value. The example below is updated for expected data / output.

With this data:

 MyTableID UserID Counter --------- ------- -------- 1 1 4 2 1 7 3 4 3 4 11 9 5 11 3 6 4 6 ... 9 11 9 

I want these results to be repeated in MAX values, select the first event in any order that the SQL server selects. Which rows are returned is not important in this case, if the UserID / Counter pairs are different:

 MyTableID UserID Counter --------- ------- -------- 2 1 7 4 11 9 6 4 6 

Answer 1

I like to use the Common Table Expression for this case with the appropriate ROW_NUMBER () function:

 WITH MaxPerUser AS ( SELECT MyTableID, UserID, Counter, ROW_NUMBER() OVER(PARTITION BY userid ORDER BY Counter DESC) AS 'RowNumber' FROM dbo.MyTable ) SELECT MyTableID, UserID, Counter FROM MaxPerUser WHERE RowNumber = 1 

TH splits the data by UserID, orders it by the counter (descending) for each user, and then puts each of the lines, starting with 1 for each user. Select only those lines with 1 for a roll and you have max. values ​​for the user.

It is so simple :-) And I get the results something like this:

 MyTableID UserID Counter 2 1 7 6 4 6 4 11 9 

Only one entry for each user, regardless of how many rows per user has the same maximum value.

Answer 2

I think this will help you.

 SELECT distinct(a.userid), MAX(a.counterid) as counterid FROM mytable a INNER JOIN mytable b ON a.mytableid = b.mytableid GROUP BY a.userid 

Answer 3

There are several ways to do this, take a look at this. Including related column values ​​with a joined column Several methods are displayed, including performance differences

Here is one example.

 select t1.* from( select UserID, max(counter) as MaxCount from MyTable group by UserID) t2 join MyTable t1 on t2.UserID =t1.UserID and t1.counter = t2.counter 

Answer 4

 select m.* from MyTable m inner join ( select UserID, max(Counter) as MaxCounter from MyTable group by UserID ) mm on m.UserID = mm.UserID and m.Counter = mm.MaxCounter 

Answer 5

Try this ... I'm sure this is the only way to make sure you get one line per user.

 SELECT MT.* FROM MyTable MT INNER JOIN ( SELECT MAX(MID.MyTableId) AS MaxMyTableId, MID.UserId FROM MyTable MID INNER JOIN ( SELECT MAX(Counter) AS MaxCounter, UserId FROM MyTable GROUP BY UserId ) AS MC ON (MID.UserId = MC.UserId AND MID.Counter = MC.MaxCounter) GROUP BY MID.UserId ) AS MID ON (MID.UserId = MC.UserId AND MID.MyTableId = MC.MaxMyTableId)