Nested Select Statement Problem

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

Sponsor our Newsletter | Privacy Policy | Terms of Service