Group By select

Question

I have an Employee table with the fields Name, Age, Gender and Company. And each employee may or may not be in more than one company.

With that I made the following select in this table.

select Nome, Empresa from funcionario group by Nome, Empresa

It’s just that obviously when I have the same employee for more than one company, the registration is duplicated, and I didn’t want that to happen, thus, I made a back-end approach to solve this problem by taking only one of the records.

var listaFuncionarios = funcionarios.GroupBy(e => e.Nome).Select(e => e.FirstOrDefault()).ToList();

Question: How to resolve this in the query itself without having to change the back-end the way I did ?

Answer 1

Your table funcionario is not normalized because the field nome can repeat of value for different companies, for the same employee,

nome                 | empresa
Mário da Silva Sauro | Toaldo Túlio Ltda.
Mário da Silva Sauro | João Falarz Ltda.

^^^^^^^^^^^^^^^^^^^^
desnormalização

You can do exactly what you did on LINQ: group by name and get the first record that matches the name,

SELECT f1.nome, f3.empresa
FROM   (SELECT nome
        FROM   funcionario
        GROUP BY nome) f1
CROSS APPLY (SELECT f2.empresa
             FROM funcionario f2
             WHERE f1.nome = f2.nome
             ORDER BY f2.empresa
             OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) f3;

The @anonimo user comment also serves:

SELECT nome, MAX(empresa)
FROM funcionario
GROUP BY nome

See also on fiddle, here.