Query assistance

Question

Post reply

Query assistance

mcoleman15902

SSC Eights!

Points: 880
More actions
September 10, 2015 at 11:16 am

#311422

Hello Masters,
I have a query that I need to be able to pull the highest value for each row. Here is the query:
SELECT
V.VEND_ID
, V.AP_CHK_VEND_ID
, V.VEND_NOTES
, V.VEND_NAME AS VEND_NAME
, V.VEND_LONG_NAME
, V.VEND_NAME_EXT
, PV.PLAT_VENDORKEY
, A.ADDR_DC
,A.ROWVERSION
, '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
, A.CITY_NAME
, A.MAIL_STATE_DC
, A.POSTAL_CD
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", and VEND_ID I believe is the unique identifier for the rowversion.
Thanks as always !

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

djj SSCoach Points: 18831 More actions · Answer 1

See http://forums.sqlteam.com/t/pull-highest-value-in-column/3155

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Here are 2 (untested) options:

WITH CTE AS(

SELECT

V.VEND_ID

, V.AP_CHK_VEND_ID

, V.VEND_NOTES

, V.VEND_NAME AS VEND_NAME

, V.VEND_LONG_NAME

, V.VEND_NAME_EXT

, PV.PLAT_VENDORKEY

, A.ADDR_DC

, A.ROWVERSION

, '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

, A.CITY_NAME

, A.MAIL_STATE_DC

, A.POSTAL_CD

, ROW_NUMBER() OVER( PARTITION BY V.VEND_ID ORDER BY A.ROWVERSION) rn

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%'

)

SELECT VEND_ID

, AP_CHK_VEND_ID

, VEND_NOTES

, VEND_NAME

, VEND_LONG_NAME

, VEND_NAME_EXT

, PLAT_VENDORKEY

, ADDR_DC

, ROWVERSION

, ADDERESS

, CITY_NAME

, MAIL_STATE_DC

, POSTAL_CD

FROM CTE

order by VEND_ID;