CODEWITHSUNDEEP
sqloracle-database
tintin
tintin

Reputation: 91

SQL :query to get values with highest Rank grouped by another column

I have sales data as follows:

Sales Region    Employee    Sales Qty   Rank
Region 1         Saly              120    1
Region 1         Mark              100    2
Region 1         Tom                65    3
Region 1         Chris              23    4
Region 2         Liam               45    1
Region 2         Kent               29    2
Region 2         Paul               21    3

what query will get the QTY with Rank=1 for each Region in each row like as follows:

 Sales Region   Employee    Sales Qty   Rank  MaxRegional Sales
    Region 1         Saly       120     1         120
    Region 1         Mark       100     2         120
    Region 1         Tom         65     3         120
    Region 1         Chris       23     4         120
    Region 2         Liam        45     1          45
    Region 2         Kent        29     2          45
    Region 2         Paul        21     3          45

Upvotes: 0

Views: 481

Answers (2)

Sergey Afinogenov
Sergey Afinogenov

Reputation: 2202

You may use KEEP DENSE_RANK FIRST with PARTITION BY. Have a look at this example:

 SELECT last_name, department_id, salary,
   MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
   OVER (PARTITION BY department_id) "Worst",
   MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
   OVER (PARTITION BY department_id) "Best"
   FROM employees
  ORDER BY department_id, salary;

But in your case it seems that you just try to find max value in group and you may use MAX OVER PARTITION BY.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Use a window function:

select t.*,
       max(case when rank = 1 then sales_qty end) over (partition by sales_region) as max_region_sales
from t;

Note that conditional logic using the rank isn't really needed if you just want the maximum:

select t.*,
       max(sales_qty) over (partition by sales_region) as max_region_sales
from t;

Upvotes: 1

Related Questions