Query rows having maximum value in a column

Question

Query rows having maximum value in a column

Navigation

#1 by (1 votes)

1

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

ID_Loja | Channel | Sales_Value

There is the possibility of having more than one sale per store and I want to know which channel is assigned to the store based on the amount of sales each store had as per the example below. In this case, I want the query to just return the line 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

Channel: A

Count (Sales_Value): 5

ID_Loja: 1

Channel: B

Count (Sales_Value): 4

So far, I only made query which brings the amount of sales per store / channel, but I was not able to only bring 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

asked by anonymous 21.05.2017 / 23:00

1 answer

How to make two RadioButtons enable and disable fields Foreach does not have marked fields

score 1 · Answer 1

In the example below you can see that the A Channel and the B Channel appear for more than one store, but only the store that sold the most to that channel will be shown: / p>

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.