mySQL query with JOIN on latest record not all records

Question

The following code is used in a query for fetching records. It uses the electors.ID to find the corresponding voting_intention.elector from a second table.

$criteria = "FROM voting_intention,electors WHERE voting_intention.elector = electors.ID AND voting_intention.pledge IN ('C','P') AND electors.postal_vote = 1 AND electors.telephone > 0"

The problem is that some electors will have more than one pledge in the voting_intentions table.

I need it to match only on the latest voting_intention.pledge based on the field votin_intention.date for each elector.

What is the simplest way of implementing that.

The rest of the code:

function get_elector_phone($criteria){
    $the_elector = mysql_query("SELECT * $criteria ORDER BY electors.ID ASC"); while($row = mysql_fetch_array($the_elector)) { 
    echo $row['ID'].','; }  
}

score 2 · Accepted Answer · answered Jun 28 '12 at 17:47

2

You could use a sub-select with the MAX() function. Add the following into your WHERE clause.

AND voting_intention.date = (select MAX(date) 
                         from voting_intention vote2 
                         where voting_intention.elector = vote2.elector)

Here is a SQLFiddle of the results.

answered Jun 28 '12 at 17:47

Seth

322
1
7

1

A man talking my language. I didn't know there was an SQLFiddle thats very good – Walrus Jun 28 '12 at 17:52
Yeah, just like JSFiddle. I love to use it, because it lets me test my answers before just tossing them out there. Plus, it lets the submitter verify the answer and even try out other changes. – Seth Jun 28 '12 at 18:01
One slight disclaimer... this answer does depend on individual electors not having multiple rows in the voting_intention table with the exact same date. Based on your naming, I assumed that it was pretty unlikely. – Seth Jun 28 '12 at 18:03
Extremely unlikely. If it happened it would be because someone entered incorrectly. – Walrus Jun 28 '12 at 18:23
We could be even safer though and add the time. – Walrus Jun 28 '12 at 18:24

score 1 · Answer 2 · edited May 23 '17 at 12:27

So pretty much, you only want to bother looking at the most recent row that fits the first two criteria in your code. In that case, you would want to filter out the voting_intention table beforehand to only have to worry about the most recent entries of each. There's a question/answer that shows how do do that here.

Try selecting the following instead of voting_intention (from the answer of the linked question, some table and field names replaced):

SELECT voting_intention.*
FROM voting_intention
INNER JOIN
(
SELECT elector, MAX(date) AS MaxDate
FROM voting_intention
GROUP BY elector
) groupedintention ON voting_intention.elector = groupedintention.elector 
AND voting_intention.date = groupedintention .MaxDate

Question url: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

mySQL query with JOIN on latest record not all records

2 Answers2