How to do SQL one-to-many highest date selection

[kuau]

Scenario: Sales agents are assigned certain clients whom they must contact at least once every six months or the client reverts to a house client.


client table: client_id, name, agent, etc

history table: client_id, actiondate, notes
This is what I need to do but don't quite know how...

Change the agent to HS in the client table if the agent hasn't contacted the client in 6 months. The history table holds multiple entries for each client with different dates. The agent changes to HS only if the most recent date for each client_id is more than 6 months ago. How do I address only the highest date for each client_id? Do I have to use a loop and do it one at a time? There are 36,000+ clients so it cannot be done manually.

Any assistance would be much appreciated.

[Butterfly]

If I understand your post correctly, I'd say you could make a selection, order it DESC and limiting it to 1:


SELECT * FROM history_table ORDER BY date DESC

Also, put it in a custom loop and run it for all 36.000 records.

[kuau]

Thanks very much! I had to use 2 WHILE loops but I believe it is doing what I needed it to do. Today is my birthday.. nice present. Mahalo, e :)