MYSQL query to return most recent entry as long as it is equal to XXX OK I'll try and explain this as best as I can as I couldnt really fit it into the title.
Let's say I have a database like so:
+----------+--------------+---------------+---------+
| Agent    | Customer ID  |    date       | Answer  |
+----------+--------------+---------------+---------+
| Jon      | 1            | 2017-05-08    |     Yes |
| Jon      | 2            | 2017-05-01    |      No |
| Jon      | 1            | 2017-03-05    |   Maybe |
| Jon      | 4            | 2017-03-10    |   Maybe |
| Jon      | 2            | 2017-03-10    |   Maybe |
| Jon      | 3            | 2017-03-10    |   Maybe |
+----------+--------------+---------------+---------+

I want to create a query that searches all of Jons entries, where he has selected &quot;Maybe&quot; for a customer, and where &quot;maybe&quot; is his most recent entry for said customer. Does this make sense? So out of the above table I only want to return the results for customer ID 3 and 4.
| Jon      | 3            | 2017-03-10    |   Maybe |
| Jon      | 4            | 2017-03-10    |   Maybe |

I dont want records 1 and 2 because there is a more updated entry of &quot;yes&quot; and &quot;no&quot;. I simply want it to check all of Jon's entries where the customer is still undecided, and there has been no new record added to state &quot;yes&quot; or &quot;no&quot;.
I've tried using MAX(date) and then running WHERE clauses of &quot;agent=xx&quot; and &quot;answer=xx&quot; however Max does not return the entire row for the most recent entry. Just the value. It appears I am in way over my head here and I don't want to resort to grabbing data from the table and sorting through it in PHP. I'm sure there is a way to write a query that does what I am looking for?
with AD as (
select T.*, rank() over (partition by Agent, customer_id order by date desc) R from tableName T
 /* order by agent, customer_id, date desc */
 )
select * from AD
  where R = 1 and answer = 'Maybe';

See db fiddle
For people trying to answers questions like these, tableconvert comes in handy.  Use Import / Markdown.