CREATE VIEW dbo.IHR_MALZEME_SON_FIYAT_FIRMA
AS
SELECT QQ.LOGICALREF,QQ.DEFINITION_,QQ.PRICE,QQ.CURRENCY,QQ.REF FROM
(
SELECT
ITM.LOGICALREF, 
CMP.DEFINITION_, 
PRC.PRICE, 
CASE WHEN PRC.CURRENCY = 13 THEN 'RMB' WHEN PRC.CURRENCY = 1 THEN 'USD' ELSE 'TL' END AS CURRENCY, 
CMP.REF,
ROW_NUMBER() OVER(PARTITION BY ITM.LOGICALREF ORDER BY PRC.PRICE DESC) AS SIRA
FROM            dbo.PRCLIST AS PRC LEFT OUTER JOIN
                         dbo.ITEMS AS ITM ON ITM.REF = PRC.ITEMREF LEFT OUTER JOIN
                         dbo.COMPANIES AS CMP ON CMP.REF = PRC.COMPANYREF
WHERE        (PRC.ORDER_ =
                             (SELECT        MAX(ORDER_) AS Expr1
                               FROM            dbo.PRCLIST
                               WHERE        (ITEMREF = ITM.REF))) AND (CMP.REF <> 15))QQ
							   WHERE QQ.SIRA=1