CODEWITHSUNDEEP
sqlsql-server
Ciel
Ciel

Reputation: 6291

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

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)

Upvotes: 0

Views: 59

Answers (1)

vhadalgi
vhadalgi

Reputation: 7189

In sql-server :

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

Upvotes: 3

Related Questions