Help with a SQL Query

[ricolame]

Hi folks,

My brain is going abit dead.. need some technical advise on sql query..

VENDOR_QUOTE table : vendor_id,partid,quote_date,default_price

for each part, i can have few vendors
for each part, i can have few quote dates

i need to know how i should write my sql such that,
it will return the latest quote price for each pair of vendor and its supplying part.

Any advise here? Thanks.

 

[blom0344]

Depending on your platform, this is a solution with the OLAP function rank():

SELECT TEMP.V,TEMP.P,TEMP.DP FROM
(SELECT TABLE.VENDOR AS V,TABLE.PART AS P,TABLE.DEFAULT_PRICE AS DP,
(RANK() OVER (PARTION BY TABLE.VENDOR,TABLE.PART ORDER BY TABLE.QUOTE_DATE DESC) AS RANK)) TEMP
WHERE TEMP.RANK = 1

Ties Blom

 

[ricolame]

ah, i'm on Sqlbase platform

 

[blom0344]

SELECT DISTINCT T.* FROM table t, table tt
WHERE T.vendor = TT.vendor AND T.part = TT.part AND
T.quote_date = (SELECT MAX(quote_date) FROM table TT WHERE 
T.vendor = TT.vendor AND T.part = TT.part)

Well, something along those lines. This is a quick test on ORACLE

Ties Blom

 

[r937]

select vendor_id
     , partid
     , quote_date
     , default_price
  from VENDOR_QUOTE as X
 where quote_date 
     = ( select max(quote_date) 
           from VENDOR_QUOTE 
          where vendor = X.vendor 
            and part = X.part )

r937.com | rudy.ca

 

[PHV]

Another way:
SELECT A.vendor_id,A.partid,A.quote_date,A.default_price
FROM VENDOR_QUOTE A INNER JOIN (
SELECT vendor_id,partid,MAX(quote_date) LastDate FROM VENDOR_QUOTE GROUP BY vendor_id,partid
) B ON A.vendor_id=B.vendor_id AND A.partid=B.partid AND A.quote_date=B.LastDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886