I'm just starting with PostgreSQL and I bet there is a simple answer for my qs.

I have a view like:

 county - candidate - votes
Abbeville - John Kasich - 157
Abbeville - Ben Carson - 305
Abbeville - Ted Cruz - 876
Abbeville - Jeb Bush - 236
Abbeville - Hillary Clinton - 1501
Abbeville - Marco Rubio - 740
Abbeville - Bernie Sanders - 312
Abbeville - Donald Trump - 1353
Abbot - Hillary Clinton - 0
Abbot - Bernie Sanders - 1
Abington - Ben Carson - 53
Abington - John Kasich - 299
Abington - Bernie Sanders - 1352

And I would like to write a query which returns who has most votes per county like:

    county - candidate - votes
    Abbeville - Hillary Clinton - 1501
    Abbot - Bernie Sanders - 1 
    Abington - Bernie Sanders - 1352 

I tried this query but cannot return right candidate:

distinct on (county) county , candidate ,max(votes) over(partition by county)
from primary_results_csv prc 
group by county, candidate, votes
order by county

Thanks in advance for help.


You were close, but you don't need the group by or the max(votes) window function when using distinct on ()

select distinct on (county) *
from primary_results_csv
order by county, votes desc;

The equivalent solution using window functions would be:

select county, candidate, votes
from (
   select *, max(votes) over (partition by county) as max_votes
   from primary_results_csv
) t
where votes = max_votes;

Online example

  • Awesome! Thank you! I used the equivalent solution and it was a perfect match to what I needed. So it's solved :) Oct 14 '20 at 13:37
  • @Grzegorz-so - you might (unlikely though it may be) to be careful of a situation where a draw occurs - the result is non-deterministic using the SQL above - see here. It would be entirely unfair to give a candidate victory on the basis that they were inserted into a database first - presumably arbitrary? It's also worth nothing that the window function version of the query which does pull out ties. Mar 28 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.