I have 2 tables; customer and conversation. The conversation table records stages that a client is at, for example client 1 may have 3 conversations, one at stage 1, one at stage 2, and one stage 5.
The only information I need it the highest stage for each client. I created this query below which accomplishes this.
SELECT customer.ClientID, MAX(conversation.stage)
FROM customer LEFT JOIN conversation ON customer.ClientID=conversation.cClientID
GROUP BY customer.ClientID
The problem is I need to total each of these stages so I need to find out how many are at stage 1, how many stage 2 etc. But it must only count the highest stage and ignore any previous stages a client may have.
I have tried different nested selects but just can’t get it to work.
Please help