Pull highest value in column

Transact-SQL
1

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"

Thanks as always !

2

Reformatting for readability (poorsql.com + </> tags from toolbar

   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
3

other than the rowversion column, what column makes a row unique?

4

VEND_ID should do it

5

then do a self join

join (select max (rowversion), vendid from WEBAPP_CP.DELTEK.VEND_ADDR gropy by vend_id)  q
on q.vend_id = a.vend_id and q.rowversion = a.rowversion

or something like that

6

Change

LEFT JOIN WEBAPP_CP.DELTEK.VEND_ADDR A
          ON a.vend_id = v.vend_id

to an OUTER APPLY perhaps?

OUTER APPLY
(
    SELECT TOP 1 A.ADDR_DC, A.ROWVERSION, ... etc ...
    FROM WEBAPP_CP.DELTEK.VEND_ADDR AS A
    WHERE a.vend_id = v.vend_id
    ORDER BY rowversion DESC
) AS A
1 Like
7

Personally, assuming that "Get most recent Vendor Address" is the most common request I would do something to facilitate that.

Either de-normalise and store the A.rowversion in the V_VEND table (don't like that idea much!!) or add a Flag to VEND_ADDR to indicate "IsCurrent" and have a constraint that only one record, for a given vend_id value, is allowed to have IsCurrent set.

A filtered index will probably help with performance a lot:

CREATE UNIQUE INDEX CurrentAddress
ON VEND_ADDR
(
    vend_id
)
WHERE IsCurrent = 1

I have no idea if I have got the Syntax for the filtered index even close-to-being-right!!

You will need to include "WHERE IsCurrent = 1" in the Query to ensure that that index is used (assuming Optimiser considers it - but it will NOT consider it if the Index Filter Where clause is not repeated in the Query itself)

1 Like