CODEWITHSUNDEEP
sqlinner-joinmin
Juice
Juice

Reputation: 11

List vendors, and lowest priced product SQL

I am trying to join 2 tables and create a new field returning the lowest value of a product. I've tried many variations and can't seem to get it to work. 

SELECT DISTINCT VENDOR.*, PRODUCT.P_PRICE, PRODUCT.LOWEST_PRICE AS MIN(PRODUCT.P_PRICE)
FROM PRODUCT
INNER JOIN VENDOR
ON VENDOR.V_CODE = PRODUCT.V_CODE
ORDER BY VENDOR.V_NAME

Upvotes: 0

Views: 831

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If you want the lowest price product per vendor, I would suggest using window functions:

select pv.*
from (select v.v_name, p.p_name, p.p_price
             row_number() over (partition by p.v_code order by p.p_price asc) as seqnum
      from vendor v join
           product p
           on v.v_code = p.v_code
     ) pv
where seqnum = 1

Upvotes: 0

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

You can use this, This will order from Minimum price vendor product

SELECT  VENDOR.V_NAME,  MIN(PRODUCT.P_PRICE) AS LOWEST_PRICE 
FROM VENDOR
INNER JOIN PRODUCT
ON VENDOR.V_CODE = PRODUCT.V_CODE
GROUP BY VENDOR.V_NAME
ORDER BY LOWEST_PRICE

SQL FIDDLE:- http://sqlfiddle.com/#!3/467c8/2

Upvotes: 1

Stephen Bodine
Stephen Bodine

Reputation: 519

Not knowing how your schema is defined try this:

SELECT 
    VENDOR.V_NAME, 
    PRODUCT.P_PRICE AS [VENDOR PRICE], 
    MIN(PRODUCT.P_PRICE) AS [LOWEST PRICE]
FROM 
    PRODUCT 
    INNER JOIN VENDOR ON VENDOR.V_CODE = PRODUCT.V_CODE 
GROUP BY
    VENDOR.V_NAME,PRODUCT.P_PRICE
ORDER BY 
    VENDOR.V_NAME

Upvotes: 0

crthompson
crthompson

Reputation: 15865

If you are looking for the lowest price by vendor, use group by and min:

SELECT  VENDOR.V_NAME,  MIN(PRODUCT.P_PRICE) AS LOWEST_PRICE 
FROM PRODUCT
INNER JOIN VENDOR
ON VENDOR.V_CODE = PRODUCT.V_CODE
GROUP BY VENDOR.V_NAME
ORDER BY VENDOR.V_NAME

Upvotes: 1

Related Questions