I have a query that I need to be able to pull the highest value for each row. Here is the query:
, V.VEND_NAME AS VEND_NAME
, 'L1: '+A.LN_1_ADR+' '
+CHAR(13) +CASE WHEN A.LN_2_ADR = '' THEN '' ELSE ('L2: '+A.LN_2_ADR) END
+CHAR(13) +CASE WHEN A.LN_3_ADR = '' THEN '' ELSE (' '+'L3: '+A.LN_3_ADR) END as ADDERESS
FROM WEBAPP_CP.DELTEK.V_VEND V
Left JOIN CP_Migration.dbo.L_CP_Plat_Vendorkey PV
ON V.VEND_ID = PV.CP_Vendorkey
LEFT JOIN WEBAPP_CP.DELTEK.VEND_ADDR A
ON a.vend_id = v.vend_id
WHERE VEND_NAME_EXT like '%ADD%'
order by VEND_ID
So if someone makes a change to a vendor address, it creates a new row with the new address and a new "RowVersion" number is created. Right now this query will pull both addresses. I need it to pull the row with the highest rowversion number. The datatype for the RowVersion column is "int"
Thanks as always !