7Xi0Vt9r

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;
50 
51 /*
52 drop table if exists candidate;
53
54create table candidate
55(
56candidate_id int unsigned not null primary key
57);
58
59insert into candidate values (1),(2),(3);
60
61drop table if exists voter;
62
63create table voter
64(
65voter_id int unsigned not null primary key
66);
67
68insert into voter values (10),(20),(30),(40),(50),(6);
69
70drop table if exists vote;
71
72create table vote
73(
74vote_id int unsigned not null auto_increment primary key, -- not my choice of PK but i want duplicates
75candidate_id int unsigned not null,
76voter_id int unsigned not null
77);
78                                                                        
79insert into vote (candidate_id, voter_id) values 
80(1,10),(1,20),(1,30),
81(2,40),(2,50),
82(3,60),
83(2,10); -- voter 2 voting for another candidate
84
85
86select 
87 v.*
88from 
89 vote v
90inner join
91(
92select
93  min(vote_id) as vote_id,
94  voter_id
95 from
96  vote
97 group by
98  voter_id
99 ) max_vote on v.vote_id = max_vote.vote_id;
100
101/*
102
103vote_id	candidate_id	voter_id
104=======	============	========
1051	                1	        10
1062	                1	        20
1073	                1         30
1084	                2	        40
1095	                2	        50
1106	                3	        60
111
112*/