Im trying to find the distributor with the highest and lowest quantity for each country in two columns distributor with minimum quantity and maximum quantity I have been able to get the information from other posts but it is in a column however I want it on a row per country

See http://sqlfiddle.com/#!17/448f6/2

Desired result

"country"   "min_qty_name"  "max_qty_name"
1. "Madagascar" "Leonard Cardenas" "Gwendolyn Mccarty"
2. "Malaysia"   "Arsenio Knowles" "Yael Carter" 
3. "Palau"      "Brittany Burris" "Clark Weaver"  
4. "Tanzania"   "Levi Douglas" "Levi Douglas"

CodePudding user response：

You can use subqueries:

select distinct country,
(select distributor_name 
 from product 
 where country = p.country 
 order by quantity limit 1) as min_qty_name,
 (select distributor_name 
 from product 
 where country = p.country 
 order by quantity desc limit 1) as max_qty_name
 from product p;

Fiddle

CodePudding user response：

You can do this with a single sort and pass through the data as follows:

with min_max as (
  select distinct country, 
         first_value(distributor_name) over w as min_qty_name,
         last_value(distributor_name) over w as max_qty_name
    from product
  window w as (partition by country 
                   order by quantity
            rows between unbounded preceding
                     and unbounded following)
)
select * 
  from min_max
 order by min_max;

Updated Fiddle

CodePudding user response：

You can do it with cte too (result here)

WITH max_table AS
(      
    SELECT ROW_NUMBER() OVER (partition by country order by country,quantity DESC) AS rank,
    country, quantity,distributor_name
    FROM
    product
),
min_table AS
(      
    SELECT ROW_NUMBER() OVER (partition by country order by country,quantity) AS rank,
    country, quantity,distributor_name
    FROM
    product
)
SELECT m1.country,m2.distributor_name,m1.distributor_name
from max_table m1, min_table m2
where m1.country = m2.country
and m1.rank = 1 and m2.rank = 1