MYSQL query to return most recent entry as long as it is equal to XXX

MySQLquery

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 "Maybe" for a customer, and where "maybe" 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 "yes" and "no". 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 "yes" or "no".

I've tried using MAX(date) and then running WHERE clauses of "agent=xx" and "answer=xx" 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?

Best Answer

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.

Related Question