[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.