By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table primary_results_csv (county text, candidate text, votes int);
insert into primary_results_csv
values
('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)
;
select distinct on (county) *
from primary_results_csv
order by county, votes desc;
select county, candidate, votes
from (
select *, max(votes) over (partition by county) as max_votes
from primary_results_csv
) t
where votes = max_votes
order by county
CREATE TABLE
INSERT 0 13
| county | candidate | votes |
|---|---|---|
| Abbeville | Hillary Clinton | 1501 |
| Abbot | Bernie Sanders | 1 |
| Abington | Bernie Sanders | 1352 |
SELECT 3
| county | candidate | votes |
|---|---|---|
| Abbeville | Hillary Clinton | 1501 |
| Abbot | Bernie Sanders | 1 |
| Abington | Bernie Sanders | 1352 |
SELECT 3