Count records based on most recent

[matthewra]

I want to count the votes for each candidate limited it to the most recent vote for each email address (each person can vote more than once).

<p>

I can do:<hr>

SELECT x.vote_vote, x.vote_email, x.id from voteiah2011 as x

where x.id  =

( SELECT max(ID) from voteiah2011 where vote_email = x.vote_email)

<hr>

to get the most recent votes for each email

<p>

and I can do

<hr>

select count(*) as v, vote_vote from `voteiah2011` group by `vote_vote` order by v DESC

<hr>

to count votes for each candidate (vote_vote)

<p>

But how do I count the votes for each candidate for only the latest vote (Maximum value of ID which is autonumber field in table)

[Ken2k7]

Define "count the votes". If you only want the latest vote, wouldn't count be at most 1? Can you lay out an example that I can use to help you with this? And also, HTML doesn't work here.

[matthewra]

Sorry for the poor explanation.

A user may register an email address and then vote (select 1 of 3 values).

The table called "iahvote2011' has 3 important fields:

1. ID (autonumber)

2. vote_email (the email address)

3. vote_vote ("A", "B", or "C")

 

A user may register the email address more than once and vote more than once.

 

I can get the simple total number of votes for "A" and "B" and "C"

 

I need a query that give me a count of votes for A, B and C for only the most recent vote for all email addresses. If there is more than one vote_vote value for the same vote_email value. only count the most recent (highest value of ID) for all records with same value of vote_email.

 

If there is exactly 1 vote per email the results will be the same.

[Ken2k7]

Explanation is still pretty poor, but I think this is what you want -

SELECT COUNT(DISTINCT vote_email) FROM iahvote2011 GROUP BY vote_vote ORDER BY vote_vote;

 

That would give you 3 rows. First one for A, second for B, third for C.