Fetch the row which has the Max value for a column in SQL Server

Question

I found a question that was very similar to this one, but using features that seem exclusive to Oracle. I'm looking to do this in SQL Server.

I have a table like this:

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

Each user can have multiple rows, with different values for Counter in each row. I need to find the rows with the highest Counter value for each user.

How can I do this in SQL Server 2005?

The best I can come up with is a query the returns the MAX(Counter) for each UserID, but I need the entire row because of other data in this table not shown in my table definition for simplicity's sake.

EDIT: It has come to my attention from some of the answers in this post, that I forgot an important detail. It is possible to have 2+ rows where a UserID can have the same MAX counter value. Example below updated for what the expected data/output should be.

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 for the duplicate MAX values, select the first occurance in whatever order SQL server selects them. Which rows are returned isn't important in this case as long as the UserID/Counter pairs are distinct:

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

Answer 1

There are several ways to do this, take a look at this Including an Aggregated Column's Related Values Several methods are shown including the 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 2

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 3

I like to use a Common Table Expression for that case, with a suitable ROW_NUMBER() function in it:

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

THat partitions the data over the UserID, orders it by Counter (descending) for each user, and then labels each of the rows starting with 1 for each user. Select only those rows with a 1 for rownumber and you have your max. values per user.

It's that easy :-) And I get results something like this:

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

Only one entry per user, no matter how many rows per user happen to have the same max value.

Answer 4

Try this... I'm pretty sure this is the only way to truly make sure you get one row 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)