Getting hightest and lowest price and seller in a query-CodePudding

cod_Prod   product seller price
  123        A     XPTO    10
  123        A     POTY    20
  123        A     MUY     15

And I querying max and min price of the producs as below:

select cod_Prod, product, max(price) as maxprice, min(price) as minprice
from tablename
group by cod_Prod, product

And I get this, which is correct:

cod_Prod product maxprice  minprice
  123      A       20       10

How can I get the seller with the highest and lowest price in the same query, like this output:

cod_Prod product maxprice seller_maxprice minprice seller_minprice
  123      A        20        POTY          10           XPTO

CodePudding user response：

Using ROW_NUMBER with pivoting logic would be one way to go here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY cod_Prod ORDER BY price) rn_low,
              ROW_NUMBER() OVER (PARTITION BY cod_Prod ORDER BY price DESC) rn_high
    FROM tablename
)

SELECT
    cod_Prod,
    product,
    MAX(price)  FILTER (WHERE rn_high = 1) AS maxprice,
    MAX(seller) FILTER (WHERE rn_high = 1) AS seller_maxprice,
    MAX(price)  FILTER (WHERE rn_low = 1)  AS minprice,
    MAX(seller) FILTER (WHERE rn_low = 1)  AS seller_minprice
FROM cte
GROUP BY
    cod_Prod,
    product;

Demo

CodePudding user response：

A slightly heavier approach, that will require the plugins for cod_Prod and product -

with get_id as(
select distinct cod_Prod , product from tablename where cod_Prod = 123 and product = 'A'
),

get_max as(
select st.cod_Prod , st.product , st.seller, st.price from tablename st, get_id main
    where st.cod_Prod  = main.cod_Prod and st.product = main.product 
 order by st.price desc limit 1
),

get_min as(
select st.cod_Prod , st.product , st.seller, st.price from tablename st, get_id main
    where st.cod_Prod = main.cod_Prod and st.product = main.product 
    order by st.price limit 1
)

select get_id.cod_Prod , get_id.product , get_min.price as min_price, get_min.seller as seller_minprice
, get_max.price as max_price, get_max.seller as seller_maxprice
from
get_id

left join get_max on get_id.cod_Prod = get_max.cod_Prod and get_id.product = get_max.product 
left join get_min on get_id.cod_Prod = get_min.cod_Prod and get_id.product = get_min.product