Doubt with group by - sql

Question

I have these tables:

CREATE TABLE empresa (
    id_empresa     INT,
    nome_empresa   VARCHAR(40),
    razao_social   VARCHAR(40),
    PRIMARY KEY ( id_empresa )
);

CREATE TABLE departamento (
    id_departamento     INT,
    id_empresa          INT,
    nome_departamento   VARCHAR(40),
    PRIMARY KEY ( id_departamento ),
    FOREIGN KEY ( id_empresa )
        REFERENCES empresa ( id_empresa )
);

CREATE TABLE produto (
    id_produto        INT,
    id_departamento   INT,
    descricao         VARCHAR(40),
    valor             INT,
    PRIMARY KEY ( id_produto ),
    FOREIGN KEY ( id_departamento )
        REFERENCES departamento ( id_departamento )
);

And these records:

insert into empresa(id_empresa, nome_empresa, razao_social)
  values (1, 'Casas Bahia', 'Loja de produtos diversos');

insert into departamento(id_departamento, id_empresa, nome_departamento)
  values (1, 1, 'Eletrodomesticos');

insert into departamento(id_departamento, id_empresa, nome_departamento)
  values (2, 1, 'Produtos de Limpeza');

insert into produto(id_produto, id_departamento, descricao, valor)
  values (1, 1, 'Televisao', 1200);
insert into produto(id_produto, id_departamento, descricao, valor)
  values (2, 1, 'Computador', 4600);

insert into produto(id_produto, id_departamento, descricao, valor)
  values (3, 2, 'Detergente', 2);
insert into produto(id_produto, id_departamento, descricao, valor)
  values (4, 2, 'Esponja', 6); 

I would like to return the name of the department, and the product with the highest value and its name, but it always returns values to more.

Eletrodomesticos    Computador  4600
Produtos de Limpeza Esponja     6


select d.nome_departamento, p.descricao, p.valor
    from departamento d, produto p
    inner join (
        select descricao, max(valor) as pmax
        from produto
        group by descricao
    ) gp on p.valor = gp.pmax
    where p.id_departamento = d.id_departamento;

Answer 1

I actually believe you can use a consultation like this:

select distinct d.nome_departamento, p.descricao, p.valor
from departamento d, produto p
where p.id_departamento = d.id_departamento
  and p.id_produto in (
    select top 1 p1.id_produto
    from produto p1
    where p1.descricao = p.descricao
    order by p1.valor desc
);

Removing the inner join and validating the product by id (the subconsulta takes care to bring the highest value)

Answer 2

I think this query solves your problem:

SELECT      d.nome_departamento
        ,   p2.descricao
        ,   p.valor
FROM        departamento    d
INNER JOIN  (
                SELECT      MAX(valor)      AS valor
                        ,   MAX(id_produto) AS id_produto
                        ,   id_departamento
                FROM        produto
                GROUP BY    id_departamento
            )               p   ON p.id_departamento    = d.id_departamento
INNER JOIN  produto         p2  ON p2.id_produto        = p.id_produto