How could I find a row with maximum value of a column in SQL?

Question

I have a table which looks like

phoneNumber | City      | totalPoints
12345678    | Singapore | 2000
12345679    | Singapore | 3000
23456789    | New York  | 2100
12312312    | New York  | 2200
12312343    | Beijing   | 4000

And I want to get result like

phoneNumber | City      | totalPoints
12345679    | Singapore | 3000
12312312    | New York  | 2200
12312343    | Beijing   | 4000

Just select the row which has the maximun value of totalPoints in each city. How to write the SQL code? (I am using MS SQL Server)

vhadalgi · Accepted Answer · 2014-04-11 07:48:35Z

3

In sql-server :

select * from
(
select *,rn=row_number()over(partition by City order by totalpoints desc) from table
)x
where rn=1

answered Apr 11, 2014 at 7:48

vhadalgi

7,2376 gold badges44 silver badges67 bronze badges

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

3 Comments

Ciel Over a year ago

Hi, thank you! It works but it looks quite odd. I try MAX() and GROUP BY but it seems this cannot work in this case. Any better solutions? Or do I have to change my database design?

vhadalgi Over a year ago

you cannot use a max() if you did then you need to group by ... so in this case this only way left !

vhadalgi Over a year ago

oops.. i missed this part.. why you say "It works but it looks quite odd."

Collectives™ on Stack Overflow

How could I find a row with maximum value of a column in SQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related