0

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?

3
  • 1
    If you would have bothered to create a DB Fiddle, you would have gotten an answer much faster. Nov 1, 2020 at 10:27
  • BTW, "date" is not a very good name for a column. Nov 1, 2020 at 10:28
  • Also, you should add your engine version, in addition. Nov 1, 2020 at 10:59

1 Answer 1

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

2
  • +1 for the tableconvert link, I'll bookmark that one
    – Lennart
    Nov 1, 2020 at 12:49
  • I'm rather lazy, and thought "someone must have thought of this". Nov 1, 2020 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.