MySQL Voting Table, Find most recent vote by each user on an entry, and count based on value -

working database structure didn't create , hoping on simple mysql query. have voting table votes these fields:

vote_id = unique vote identifier

item_id = id of entry voted on

voter_id = id of member entered vote

vote_date = date of vote

vote = type of vote (1 or 2)

each user can vote multiple times on site entry specified item_id. can place vote of 1 "like" vote, or vote of 2 "unlike" vote. each time vote, new record created. able find recent vote value each user on particular item_id , able sum or count vote column if vote value "like" (value of 1)

for example

vote_id   item_id   voter_id   vote_date     vote 60        9         27         1273770151    1 153       9         45         1274896188    1 163       9         3          1274918584    1 164       9         3          1275021495    2 1051      9         181        1290839090    1 

i want newest vote values each user, , count of how many votes of 1 there are. in case, # 3

27  = 1 45  = 1 3   = 2 181 = 1       3 

ideally once have "score" each item_id, query able sum score of each 1 total # of current "likes" on site.

thanks this. did best find answer searching nothing right.

much appreciated.

[edit:] added values (1052, 10, 3, 1290839091, 1) better exercise problem, , had add "item_id" sub-query extracts newest votes.

---

yay! stackoverflow question can answer!!! i've been browsing around week looking short & sweet & alley. fun sql problem!

first, need extract newest votes. combination of (voter_id, item_id, vote_date) better unique approach work!

select   voter_id, item_id, max(vote_date) vote_date   votes group   voter_id, item_id 

results:

+----------+---------+------------+ | voter_id | item_id | vote_date  | +----------+---------+------------+ |        3 |       9 | 1275021495 | |        3 |      10 | 1290839091 | |       27 |       9 | 1273770151 | |       45 |       9 | 1274896188 | |      181 |       9 | 1290839090 | +----------+---------+------------+ 

and need join original table against these results. sub-select job. notice how query above copy & pasted join below, it's given name "tmp":

select   v.* (   select     voter_id, item_id, max(vote_date) vote_date       votes   group     voter_id, item_id   ) tmp inner join   votes v on (     v.vote_date = tmp.vote_date                , v.voter_id  = tmp.voter_id                , v.item_id   = tmp.item_id   ) 

results:

+---------+---------+----------+------------+------+ | vote_id | item_id | voter_id | vote_date  | vote | +---------+---------+----------+------------+------+ |      60 |       9 |       27 | 1273770151 |    1 | |     153 |       9 |       45 | 1274896188 |    1 | |     164 |       9 |        3 | 1275021495 |    2 | |    1051 |       9 |      181 | 1290839090 |    1 | |    1052 |      10 |        3 | 1290839091 |    1 | +---------+---------+----------+------------+------+ 

i trust know here.... oh darn it, can't myself, cute:

select   v.item_id, sum(2 - v.vote) likes, sum(v.vote - 1) dislikes (   select     voter_id, item_id, max(vote_date) vote_date       votes   group     voter_id, item_id   ) tmp inner join   votes v on (     v.vote_date = tmp.vote_date                , v.voter_id  = tmp.voter_id                , v.item_id   = tmp.item_id   ) group   v.item_id 

results:

+---------+-------+----------+ | item_id | likes | dislikes | +---------+-------+----------+ |       9 |     3 |        1 | |      10 |     1 |        0 | +---------+-------+----------+