Checking if the row has the max value in a group

Question

I'm trying get to find out if a row has the max value in a group. Here's really simple example:

Data

VoteCount   LocationId  UserId
3           1           1
4           1           2
3           2           2
4           2           1

Pseudo-query

select 
    LocationId,
    sum(case 
          when UserId  = 1  /* and has max vote count*/ 
            then 1 else 0 
        end) as IsUser1Winner,
    sum(case 
          when UserId  = 2  /* and has max vote count*/ 
            then 1 else 0 
        end) as IsUser2Winner
from LocationVote
group by LocationID

It should return:

LocationId  IsUser1Winner   IsUser2Winner
1           0           1
2           1           1

I also couldn't find a way to generate dynamic column names here. What would be the simplest way to write this query?

Answer 1

You could also do this using a Case statement

WITH CTE as 
    (SELECT 
        MAX(VoteCount) max_votes
        , LocationId
    FROM LocationResult
    group by LocationId
    )
    SELECT 
        A.LocationId 
        , Case When UserId=1
            THEN 1
            ELSE 0
            END IsUser1Winner   
        , Case when UserId=2
            THEn 1
            ELSE 0
            END IsUser2Winner   
    from LocationResult A
    inner join 
    CTE B
    on A.VoteCount = B.max_votes
    and A.LocationId = B.LocationId

Answer 2

Try this:
select *
from table t
cross apply (
select max(votes) max_value
from table ref
where ref.group = t.group
)votes
where votes.max_value = t.votes
but if your table is huge and has no propriate indexes performance may be poor
Another way is to get max values by groups into table variable or temp table and then join it to original table.