Select com group by duplo

Question

In Mysql I have a table called contact_status:

id | id_contato | detalhamento_id
1  |     1      |       2
2  |     1      |       3
3  |     2      |       4
4  |     2      |       2
5  |     2      |       1
6  |     3      |       2 
7  |     4      |       4
8  |     5      |       4

In it is recorded all the contacts of attendance of the site, and also whenever a status is modified (for history). What I need now is to list the total details of each type. I managed with GROUP BY without problems. But it is not the right one. Because it also takes duplicate registrations (if a user has modified status). Then you would need to take only the details_id of each user, but the latter, with the larger ID. What I need to be shown is this:

detalhamento 1 - 1 ocorrência
detalhamento 2 - 1 ocorrência
detalhamento 3 - 1 ocorrência
detalhamento 4 - 2 ocorrência

How to do?

Answer 1

The simplest way (maybe not the most elegant) is to do it with a subquery, this way:

Select contato_status.detalhamento_id, count(*) ocorrencias 
From contato_status 
Inner Join (Select id_contato ,MAX(id) id 
            From contato_status 
            Group By id_contato) NovaTabela
    on contato_status.id = NovaTabela.id 
Group By contato_status.detalhamento_id;

I added a Sqlfiddle to see how the query works.

How it works:

1. The subquery creates a "virtual table" (I prefer to use the term set, "set" in English), nicknamed Novatable, with the id_contact and the maximum id. You can see the result here: Isolated subquery

2. Now is to merge (Join) the set with the main table used the ID as its criterion, grouping by detail_id and counting.

As you can observe, the result obtained is:

detalhamento 1 - 1 ocorrência (para o contato_id 2)
detalhamento 2 - 1 ocorrência (para o contato_id 3)
detalhamento 3 - 1 ocorrência (para o contato_id 1)
detalhamento 4 - 2 ocorrências (para os contato_id 4 e 5)