CODEWITHSUNDEEP
sqloracle-databasegroup-by
joseph immanuel
joseph immanuel

Reputation: 1

Grouping a row based on field in a different table in oracle

I am working with these two tables for the past two days

parts_list table:

PART_ID VENDOR_ID   LABEL   
1          5        A
1          2        B
1          3        C
2          2        D
2          3        E
3          3        F

vendor_prsdnc table:

VENDOR_ID   PRSCDNC 
5           3
2           2
3           1

Can anybody please tell me how to retrieve the label of each part from the vendor with highest precedence? For example the part with id one is supplied by 3 vendors but we need the one from vendor with highest precedence ie 5. The expected result is:

PART_ID VENDOR_ID   LABEL   
1           5       A
2           2       D
3           3       F

[Vendor Id is not proportional with the precedence ]

I have this query

SELECT 
    SDS.PART_ID,
    SDSIS.VENDOR_ID,
    MAX(SDSIS.PRSCDNC) 
FROM PARTS_LIST SDS,VENDOR_PRSDNC SDSIS 
WHERE SDS.VENDOR_ID=SDSIS.VENDOR_ID 
GROUP BY SDS.PART_ID,SDSIS.VENDOR_ID;

but it does not return the expected result.

Upvotes: 0

Views: 59

Answers (1)

Gaurav Soni
Gaurav Soni

Reputation: 6338

Not tested ,but it should work i think

select part_id,vendor_id,label
from
(
 select pl.part_id
       ,pl.vendor_id
       ,pl.label
       ,vp.prscdnc
       ,max(vp.prscdnc) over (partition by pl.part_id) mx
   from part_list pl,vendor_prsdnc vp
   where pl.vendor_id=vp.vendor_id
 )
 where prscdnc =mx;

Upvotes: 1

Related Questions