Empresas
Empleos
  • Sobre nosotros
  • Soluciones
    • Publicación de vacantes
      Publica tu vacante y recibe candidatos calificados en 48h.
    • Evaluación de candidatos
      500+ pruebas técnicas y psicológicas, más anti-fraude.
    • Headhunting
      Búsqueda ejecutiva a la medida de principio a fin.
    • Nómina + EOR
      Dispersión de nómina y EOR en más de 15 países de LATAM.
  • Precios
  • Empleos

0

208
Vistas
Getting hightest and lowest price and seller in a query
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
over 4 years ago · Santiago Trujillo
2 Respuestas
Responde la pregunta

0

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;

screen capture from demo link below

Demo

over 4 years ago · Santiago Trujillo Denunciar

0

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 
over 4 years ago · Santiago Trujillo Denunciar
Responde la pregunta
Encuentra empleos remotos

¡Descubre la nueva forma de encontrar empleo!

Top de empleos
Top categorías de empleo
Empresas
Publicar vacante Precios Comercial
Legal
Términos y condiciones Política de privacidad
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomiéndame algunas ofertas
Necesito ayuda