+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL Question - Selecting Max value from duplicate records (/showthread.php?tid=26028)
MySQL Question - Selecting Max value from duplicate records - El Forum - 01-04-2010
[eluser]kyleb[/eluser]
I realize this board is mostly for CI-related questions, but I've found so many great answers by searching and reading the forums that I wanted to drop this question here as everyone here seems very knowledgeable!
I currently have a query that joins about 13 tables and returns leads that were submitted to a customer - let's say that I'm an affiliate that processes potential customers for high-end digital camera sales, for example.
My query successfully works with one exception - it returns duplicate rows for some records. Here's an example:
Code:
FirstName | LastName | LeadID | InquiryID | Email | Version
Robert | Sanders | 1 | 2 | bob@ | 100
Mike | Buns | 2 | 4 | mike@ | 120
Mike | Buns | 2 | 4 | mike@ | 670"Version" refers to the version of a form filled out.
My question is - how can I return only the record for "Mike Buns" with the highest (or lowest) version number? I tried:
Code:
WHERE FORM.VERSION = (SELECT MAX(FORM.VERSION) FROM FORM)But this limits the query to only the highest FORM.VERSION for the entire query, which is not what I want.
I hope this makes sense. Thanks in advance!
EDITED TO ADD: Never mind, I solved the problem.
MySQL Question - Selecting Max value from duplicate records - El Forum - 01-04-2010
[eluser]Craig A Rodway[/eluser]
Armed with the very little information provided, I'm going to guess you might need to use 'DISTINCT column_name' somehwere in your query.