Problema com agrupamento duplicado com subquery

[Gustavo Aparecido]

Bom dia a todos,

Estou com o seguinte script

create table funcionario( 
idfunc mediumint primary key, 
iddept mediumint not null, 
nomefunc varchar(20) not null, 
cargo varchar(20) not null, 
dtadm date not null, 
salario float not null, 
comissao float);

insert into funcionario values (1,3,'Antonio','Supervisor','10/october/2005',3700.00,600.00);
insert into funcionario values (2,5,'Vilmar','Analista','01/january/1999',4300.00,2000.00);
insert into funcionario values (3,3,'Mauro','Gerente','10/april/2010',4500.00,2500.00);
insert into funcionario values (4,3,'Paulo','Auxiliar','09/february/2016',1200.00,640.00);
insert into funcionario values (5,4,'Rogerio','Gerente','09/june/2010',4300.00,6700.00);
insert into funcionario values (6,5,'Ana','Presidente','09/april/1996',8000.00,3440.00);
insert into funcionario values (7,6,'Mauricio','Auxiliar','03/february/2014',2500.00,340.00);
insert into funcionario values (8,6,'Victor','Supervisor','06/february/2013',3400.00,440.00);
insert into funcionario values (9,2,'Jose','Analista','08/july/2016',4200.00,640.00);

O problema ocorre no phpmyadmin, também no sql live e sqlplus da oracle 11g, ao realizar uma subconsulta como esta:
select nomefunc, cargo, salario from funcionario where salario in (select max(salario) from funcionario group by cargo) order by cargo asc;

resultado da consulta:
NOMEFUNC    CARGO       SALARIO
Vilmar              Analista      4300
Mauricio          Auxiliar        2500
Mauro              Gerente       4500
Rogerio            Gerente       4300
Ana                   Presidente  8000
Antonio            Supervisor  3700

Trás o cargo Gerente duplicado, alguém saberia o porque disto? pois o agrupamento já tem a função de distinção, o mais estranho é que quando dou update no salario do analista 'Vilmar' que possue o mesmo salario que o gerente 'Rogerio', o cargo de gerente para de duplicar nesta consulta que demonstrei.

Editado Novembro 22, 2017 por Gustavo Aparecido

[Denis Courcy]

Tenta assim:

SELECT f.nomefunc, f.cargo, f.salario 
FROM funcionario f
INNER JOIN (
 SELECT idFunc 
 FROM funcionario 
 GROUP BY cargo
 HAVING MAX(salario)
 ) a1 ON a1.idFunc = f.idFunc
 ORDER BY f.cargo ASC;
;

 

[Gustavo Aparecido]

4 horas atrás, Denis Courcy disse:

Tenta assim:

SELECT f.nomefunc, f.cargo, f.salario 
FROM funcionario f
INNER JOIN (
 SELECT idFunc 
 FROM funcionario 
 GROUP BY cargo
 HAVING MAX(salario)
 ) a1 ON a1.idFunc = f.idFunc
 ORDER BY f.cargo ASC;
;

 

Deu certo não, ta dando ORA-00920: invalid relational operator  no sql live.

Não to entendendo seu código você usou having sem usar um operador relacional depois do campo, e você igualou as chaves primeiras de tabela a1 e funcionario, sendo que eu só tenho a tabela funcionario amigo.

Editado Novembro 22, 2017 por Gustavo Aparecido

[Denis Courcy]

Dá certo sim.

Respondendo suas questões. 

1)você usou having sem usar um operador relacional depois do campo

Resp. A função MAX retorna o valor máximo de uma coluna em um agrupamento. Foi colocada na cláusula HAVING porque esta cláusula só é executada APÓS a cláusula GROUP BY. Como MAX é uma função de agrupamento o HAVING entende que deve selecionar somente a(s) coluna(s) com o valor máximo.

2) e você igualou as chaves primeiras de tabela a1 e funcionario, sendo que eu só tenho a tabela funcionario amigo

Sei que você só tem uma tabela funcionário, mas deve ser de seu conhecimento que a saida de uma query é uma tabela. Neste caso eu a nomeei como `a1` e relacionei esta tabela com a tabela `f` que é a tabela de funcionário. Como a tabela `a1` é o resultado de uma query em funcionário a chave primária das duas tabelas está no mesmo campo.

Para facilitar seu entendimento a rotina abaixo realiza a mesma operação

SELECT idFunc,nomefunc,cargo,MAX(salario) 
 FROM funcionario 
 GROUP BY cargo

Mas se você quiser um resultado igual ao que pretendia anteriormente, poderá usar assim:

SELECT f.nomefunc, f.cargo, f.salario 
FROM funcionario f
INNER JOIN (
 SELECT idFunc, cargo, MAX(salario) 
 FROM funcionario 
 GROUP BY cargo
 ) a1 ON a1.idFunc = f.idFunc
 ORDER BY f.cargo ASC;