List in each position, the employees who most had change of position

Question

Good evening, everyone.

I have a little problem that I’ve been trying to solve for a while and I came to ask here.

It is as follows: I have three important entities for this solution: To make it easier, I’ll just put the "minimum fields".

• Funcionario (cod_func, name, cod_cargo)
• Position (cod_cargo, Description)
• History (cod_position, cod_func, dataInicio, dataFim)

My system is as follows. Employees always face job changes. And when this change occurs, the positions are stored in a history table with the position, the employee and the date.

In other words, you have a relationship where an employee has a position. A position has several employees.

The historical table has two foreign keys.

Now I want to select, for each position, the employees who MORE had change of position.

That is, for each position, the employee who has most changed position and is in office now. Understand?

I tried to do it this way, but I don’t know "reduce more".

SELECT c.descricao, f.nome, count(*) as qtd 
FROM historicos
JOIN funcionario f on f.cod_func = historicos.cod_func
JOIN cargo c on c.cod_cargo = f.cod_cargo
GROUP BY c.dsc_cargo, f.nome

Answer 1

I think it works , without a BD to test

Funcionario (cod_func, nome, cod_cargo)
Cargo (cod_cargo, descricao)
Historico (cod_cargo, cod_func, dataInicio, dataFim)

--history of employees

select cod_cargo,cod_func,sum(*) qtd_cargos
from   historico
group by cod_cargo,cod_func

--"champions"

select cod_cargo,cod_func
from (select cod_cargo,cod_func,sum(*) qtd_cargos
      from   historico
      group by cod_cargo,cod_func) virtual1
where qtd_cargos = (select max(qtd_cargos)
                    from (select cod_cargo,cod_func,sum(*) qtd_cargos
                          from   historico
                          group by cod_cargo,cod_func) virtual2
                    where virtual2.cod_cargo = virtual1.cod_cargo)

Maybe a solution via "analityc functions" will be cleaner but I believe that this will work in all DBMS that accepts virtual tables.

Answer 2

This answer goes to SQL Server. If you need it for another database, please indicate in the question.

SELECT c.descricao, f.nome, h.qtd
FROM (
    SELECT cod_func, qtd = count(1) over (partition by cod_func)
    from historicos
    group by cod_func
    order by qtd desc
) h
JOIN funcionario f on f.cod_func = h.cod_func
JOIN cargo c on c.cod_cargo = f.cod_cargo