Return rows with the highest value in a column

Question

I have a problem with a query in an Oracle database in a table with 3 columns:

ID_Loja | Canal | Valor_Venda

There is the possibility of having more than one sale per store and I want to know which channel assigned to the store based on the amount of sales that each store had as an example below. In that case, I want the query just return the line containing channel "A" since in that case there were 5 sales vs 4 sales to the same store with different rating.

           ID_Loja: 1
             Canal: A
Count(Valor_Venda): 5

           ID_Loja: 1
             Canal: B
Count(Valor_Venda): 4

So far, I’ve only done query that brings the amount of sales per store/channel, but I could not bring only the combination that has more sales per store/channel only.

SELECT ID_Loja, Canal, COUNT(Valor_Venda)
FROM Vendas
GROUP BY ID_loja, Canal

Answer 1

I do not have Oracle here, but try to use a temporary table, in the syntax of postgresql was like this:

with temp as (select 
    f.ID_Loja,
    f.Canal,
    count(*) as qtd
    from Vendas f
    group by f.Canal,f.ID_Loja )

select  distinct
f.ID_Loja,
t.Canal,
t.qtd
from Vendas f 
inner join temp t on t.ID_Loja = f.ID_Loja and t.qtd = (select max(qtd) from temp x where x.ID_Loja = t.ID_Loja)
order by t.qtd desc