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

Working with a database structure I didn't create and hoping for some help on a simple MySQL query. I have a voting table votes with these fields:

vote_id = unique vote identifier

item_id = id of the entry voted on

voter_id = the id of the member who entered the vote

vote_date = the date of the vote

vote = the type of vote (1 or 2)

Each user can vote multiple times on a site entry specified by item_id. They can place a vote of 1 which is a "like" vote, or a vote of 2 which is an "unlike" vote. Each time they vote, a new record is created. I would like to be able to find the most recent vote value for each user on a particular item_id and then be able to actually SUM or COUNT the vote column IF the vote value is a "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 to get the newest vote values for each user, and then do a count of how many votes of 1 there are. In this case, the # would be 3

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

Ideally once I have a "score" for each item_id, the query would be able to SUM the score of each one into a total # of ALL current "likes" on the site.

Thanks for any help with this. I did my best to find an answer to this by searching but nothing was exactly right.

Much appreciated.

---

[EDIT:] I added values (1052, 10, 3, 1290839091, 1) to better exercise the problem, and had to add "item_id" to the sub-query that extracts newest votes.

---

Yay! Finally a stackoverflow question I can actually answer!!! I've been browsing around for a week looking for something short & sweet & up my alley. Thanks for a fun SQL problem!

First, you need to extract the newest votes. The combination of (voter_id, item_id, vote_date) better be unique for this approach to work!

SELECT
  voter_id, item_id, MAX(vote_date) AS vote_date
FROM
  votes
GROUP BY
  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 then you need to join the original table against these results. A sub-select will do the job. Notice how the query above is copy & pasted into the join below, but now it's given the name "tmp":

SELECT
  v.*
FROM (
  SELECT
    voter_id, item_id, MAX(vote_date) AS vote_date
  FROM
    votes
  GROUP BY
    voter_id, item_id
  ) tmp
INNER JOIN
  votes v ON (     v.vote_date = tmp.vote_date
               AND v.voter_id  = tmp.voter_id
               AND 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 you know what to do from here.... oh darn it, I can't help myself, this is too cute:

SELECT
  v.item_id, SUM(2 - v.vote) AS likes, SUM(v.vote - 1) AS dislikes
FROM (
  SELECT
    voter_id, item_id, MAX(vote_date) AS vote_date
  FROM
    votes
  GROUP BY
    voter_id, item_id
  ) tmp
INNER JOIN
  votes v ON (     v.vote_date = tmp.vote_date
               AND v.voter_id  = tmp.voter_id
               AND v.item_id   = tmp.item_id
  )
GROUP BY
  v.item_id

Results:

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

---

Unsure of the question, is this what you're looking for?

SELECT COUNT(*) FROM votes WHERE vote = '1' AND item_id = '9'