Select row based on max value in column SQL Server

Question

How can I get the output from the input using Microsoft SQL Server? (Basically select the row per ID where vote is max).

Input

ID        Label    Vote
-----------------------
79185673    2        3
79185673    0       17
79185724    4        5
79185724    1       13
79185724    0        2
79185900    1       17
79185900    2        1
79185900    4        2
79186190    3        3
79186190    2       17

Output

ID        Label    Vote
-----------------------
79185673    0       17
79185724    1       13
79185900    1       17
79186190    2       17

Ullas · Accepted Answer · 2017-11-21 11:55:08Z

4

Use ROW_NUMBER or DENSE_RANK function to give a rank per ID in the descending order of Vote column and then select the rows having rank 1.

I prefer DENSE_RANK function, because it will give same rank for the same Vote values.

Query

;with cte as(
    select [rank] = DENSE_RANK() over(
        partition by [ID]
        order by [Vote] desc
    ), *
    from [your_table_name]
)
select [ID], [Label], [Vote] from cte
where [rank] = 1;

answered Nov 21, 2017 at 11:55

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Rushton Over a year ago

If this answer helped you please consider accepting it. Accepting answers rewards the contributor for their effort and helps others to find a working solution.

Collectives™ on Stack Overflow

Select row based on max value in column SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related