sql查询从表中选择只有最大值的计数

Question

partyname    state        constituency     candidatename   district    votes

BJP       Maharashtra   Nagpur-East     Nitin Gadkari   Nagpur       1200
AAP       Maharashtra   Nagpur-East     Arvind Kejriwal Nagpur       750
BJP       Maharashtra   Nagpur-West     Vicky           Nagpur       4800
AAP       Maharashtra   Nagpur-West     Lucky           Nagpur       3500
BJP       Maharashtra   Nagpur-North    Rakesh          Nagpur       100
AAP       Maharashtra   Nagpur-North    Ravan          Nagpur       1500

below is my expected output for above table

query select only max vlue from votes group by partyname and constituency and display like below output.

partyname       Count

BJP          2

AAP          1

bcoz bjp leads from nagpur -east and nagpur west

so it display count 2

and aap leads from nagpur - north then it display only 1 count.

plz suggest me query......

Answer 1

You can try like this:

select max(votes) from tableName group by constituency

Sql fiddle :http://sqlfiddle.com/#!2/ceb017/1