Return rows with the largest value in a [duplicate] column

Navigation
0

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 was assigned to the store based on the amount of sales each store had as shown below. In this case, I want the query to just return the row containing the "A" channel since in this case 5 sales vs. 4 sales occurred for the same store with a different rating. 

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

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

So far, I have done only the query which brings the amount of sales per store / channel, but I was not able to bring just the combination that has more sales per store / channel only. 

SELECT ID_Loja, Canal, COUNT(Valor_Venda)
FROM Vendas
GROUP BY ID_loja, Canal
    
asked by anonymous 22.05.2017 / 19:00

1 answer

0

I do not have Oracle here, but try to use a temporary table, in the postgreSQL syntax it looks 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
    
22.05.2017 / 19:15
Doubts with Position Change value of a variable through another component [ANGULAR 2]