mysql - Return more than one row when using MAX SQL -

mysql - Return more than one row when using MAX SQL -

i have 2 tables in ms sql:

table: candidate candidateid |positionid 1 1 2 1 3 1 4 2 5 2 6 2

table: voting candidateid |votecount 1 8 2 8 3 4 4 7 5 6 6 7

and want homecoming table this (returning list of candidates max votecount)

candidateid |positionid |votecount 1 1 8 2 1 8 4 2 7 6 2 7

in case more 1 row have same max vote count show 2 rows instead. tried many times returns 1 row. can help me, query can use?

this not uses dense_rank(), should works on mysql also

select t.* yourtable t inner bring together ( select positionid, max(votecount) votecount yourtable grouping positionid ) m on t.positionid = m.positionid , t.votecount = m.votecount

mysql sql-server max aggregate-functions aggregation