How to select CNPJ higher billing? - SQL -

Question

Hello, everybody!

I have a table of customers with Grupo Economico code and CNPJ, each economic group has more than one record, and I cross-checked with my Billing and Address table, so far so good.

I wanted to take the CNPJ address of higher billing and have as final result only Code Economic Group and Address, without showing the CNPJ and Billing, but I can’t.

After crossing tried the code below, but the result is wrong, the address is not CNPJ higher billing:

Select CodGrupo, NomeGrupo, First(CNPJ), Max(Fat), Cidade, UF
From tbl_EndFat 
Group By CodGrupo, NomeGrupo, Cidade, UF 
OrderBy max(fat)

Edit: My goal is for the query to return the highest billing CNPJ address for each economic group.

Any suggestions?

Answer 1

According to your question I made a solution, you said you want the economic code and address of the company with the highest turnover, however two companies may have the highest turnover, this may be a problem, but according to your question this is not a problem. Follow the code that can help you, basically is to recover only the company(s) that has the highest turnover.

If you only want the company with the highest turnover group by is unnecessary, in my view.

Select CodGrupo, NomeGrupo, Cidade, UF
From tbl_EndFat 
where Fat = (select max(fat) from tbl_EndFat)

Answer 2

My goal is for the query to return the CNPJ address of higher revenue for each economic group.

Evaluate if the following sql code returns what you need.

-- código #1
SELECT top (1) with ties 
       CodGrupo, NomeGrupo, CNPJ, Faturamento, Cidade, UF
  from ...
  order by rank() over (partition by CodGrupo
                        order by Faturamento desc);