Sql Query Grouping

Question

I have a table Votes contain data

Votes    Designation    CandidateID
 4         President      Person1
 3         President      Person2
 5         Secretary      Person5
 1         Vice-Present   Person6

I want to query the winner candidate only per designation. like

 4      President Person1
 5      Secretary Person2
 1      Vice_President Person6

Answer 1

This is a good use for row_number() or dense_rank():

select v.*
from (select v.*,
             dense_rank() over (partition by designation order by votes desc) as seqnum
      from votes v
     ) v
where seqnum = 1;

Note that this uses dense_rank(). If there is a tie, then all "winners" are included. You can use row_number() to choose an arbitrary winner.

See working SQL Fiddle demo