6dCPEcag

1drop table if exists candidate;
2
3create table candidate
4(
5candidate_id int unsigned not null primary key
6);
7
8insert into candidate values (1),(2),(3);
9
10drop table if exists voter;
11
12create table voter
13(
14voter_id int unsigned not null primary key
15);
16
17insert into voter values (10),(20),(30),(40),(50),(6);
18
19drop table if exists vote;
20
21create table vote
22(
23vote_id int unsigned not null auto_increment primary key, -- not my choice of PK but i want duplicates
24candidate_id int unsigned not null,
25voter_id int unsigned not null
26);
27                                                                        
28insert into vote (candidate_id, voter_id) values 
29(1,10),(1,20),(1,30),
30(2,40),(2,50),
31(3,60),
32(2,10), -- voter 2 voting for another candidate
33(1,10); -- voter 1 voting for candidate 1 again
34
35
36select 
37 v.*
38from 
39 vote v
40inner join
41(
42select
43  min(vote_id) as vote_id,
44  voter_id
45 from
46  vote
47 group by
48  voter_id
49 ) max_vote on v.vote_id = max_vote.vote_id;