CODEWITHSUNDEEP
sqloracle-database
kpAtCh
kpAtCh

Reputation: 107

SQL - Comparing Values

I need your help again. Here's my SQL query :

SELECT sp.pro_ID, description, price, sp.sup_ID
  FROM L4_Sup_Pro sp, L4_Products prod
 WHERE prod.our_id = sp.pro_id
   AND (sp.sup_ID = '23' OR sp.sup_ID = '75')
 ORDER BY sp.pro_ID;

And this query gives me this result :

enter image description here

I need to compare the prices of SUP_ID column having values 75 and 23, and display the cheapest price. So output will be:

PRO_ID  DESCRIPTION       PRICE   SUP_ID
101     Laser Printer     630     23
121     Color Jet Printer 223     23
302     Scanner           399     75

Upvotes: 1

Views: 118

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

You can use ROW_NUMBER() analytic function

SELECT pro_ID, description, price, sup_ID
 FROM
 (
  SELECT sp.pro_ID, description, price, sp.sup_ID,
         ROW_NUMBER() OVER (PARTITION BY description ORDER BY price ) as rn
    FROM L4_Sup_Pro sp
    JOIN L4_Products prod
      ON prod.our_id = sp.pro_id
   WHERE sp.sup_ID in (23,75) -- without quotes by considering SUP_ID is a numeric column
  )
 WHERE rn = 1
 ORDER BY pro_ID

Demo

Upvotes: 1

Himanshu
Himanshu

Reputation: 3970

You just need a group by with having

    SELECT sp.pro_ID, description, 
      min(price) , min(sp.sup_ID) 
      FROM L4_Sup_Pro sp, L4_Products 
        prod
     WHERE prod.our_id = sp.pro_id AND
  (sp.sup_ID = '23' OR sp.sup_ID = '75')
    Group by s.pro_id,description having 
      1= 
     Max(case when price=min(price) 
          then 1 
      else 0 end)
      ORDER BY sp.pro_ID;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

One method is aggregation:

SELECT sp.pro_ID, description,
       MIN(price), 
       MIN(sp.sup_ID) KEEP (DENSE_RANK FIRST ORDER BY price) as min_sup_ID
FROM L4_Sup_Pro sp JOIN
     L4_Products prod
     ON prod.our_id = sp.pro_id AND
WHERE sp.sup_ID IN (23, 75)   -- looks like a number so I dropped the quotes
GROUP BY p.pro_ID, description

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Presuming columns description, and price stored on L4_Products, you can try below query -

SELECT sp.pro_ID, prod.description, prod.price, sp.sup_ID
FROM L4_Sup_Pro sp
JOIN (SELECT our_id, description, MIN(price) price
      FROM L4_Product
      GROUP BY our_id, description) prod ON prod.our_id = sp.pro_id
WHERE sp.sup_ID = '23'
OR sp.sup_ID = '75'
ORDER BY sp.pro_ID;

Upvotes: 0

Related Questions