Query Rows having Maximum value in a column

Asked

Viewed 76 times

1

I have a problem with a query on a bench Oracle 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 this case, I want the query to just return the line containing the "A" channel since in this case there were 5 sales vs 4 sales to the same store with different rating.

Id_loja: 1

Channel: A

Count(Sale value): 5

Id_loja: 1

Channel: B

Count(Sale value): 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

Can you help me?

sql oracle

  • Leandro, I don’t quite understand what you need. Do you want to bring only the store with more sales? All other stores should not appear in the result?

  • I understood what you need, the channel should not repeat. If store 1 sold 10 on channel "A" and store 2 sold 5 pro same channel, only the Loja 1 should appear.

1 answer

1

In the example below see that the Channel To and the Canal B appear to more than one store, but only the store that sold more for this channel will be shown:

WITH table_(ID_Loja, Canal, Valor_Venda) as (
    select 1, 'A', 120 from dual union all
    select 2, 'y', 300 from dual union all
    select 1, 'A', 250 from dual union all
    select 3, 'z', 100 from dual union all
    select 3, 'z', 170 from dual union all
    select 1, 'A', 200 from dual union all
    select 1, 'A', 150 from dual union all
    select 3, 'A', 100 from dual union all
    select 3, 'A', 170 from dual union all
    select 1, 'A', 200 from dual union all
    select 1, 'A', 150 from dual union all
    select 4, 'B', 150 from dual union all
    select 3, 'B', 100 from dual union all
    select 4, 'B', 170 from dual union all
    select 4, 'B', 200 from dual union all
    select 1, 'A', 150 from dual union all
    select 3, 'z', 130 from dual )

Select * from (
  SELECT
  ID_Loja, Canal, COUNT(Valor_Venda) as qtdeVenda,
  rank() over (partition by ID_Loja order by COUNT(Valor_Venda) desc) rnk
  FROM table_
  Group By ID_Loja, Canal) query
where query.rnk = 1;

See more about the oracle rank function.

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.