SELECT
ADDR.IND_ID AS IND_ID,
ADDR.ADDRESS_ID AS ADDRESS_ID,
ADDR_STG.X_SPECIAL_INFO AS X_SPECIAL_INFO,
ADDR.STATE_CODE AS STATE_CODE,
--ROW_NUMBER() OVER(PARTITION BY ADDR.ADDRESS_ID,ADDR.IND_ID ORDER BY ADDR.ADDRESS_ID,ADDR.IND_ID) AS ROW_NUM,
FROM
ADDR_PER ADDR_STG with(nolock)
INNER JOIN IND_XREF XREF with(nolock)
ON(ADDR_STG.PER_ID=XREF.SOURCE_SYSTEM_KEY)
INNER JOIN TB_ADDRESS ADDR with(nolock)
ON (XREF.IND_ID=ADDR.IND_ID)
where rownnum=1
Can anyone tell me if I can use any other function instead of ROW_NUMBER() to remove duplicates.
The above query without ROWNUMBER produces duplicate rowsas there are duplicate records on ADDRESS and IND ID in the XREF table. Appreciate your help.
Is rownum a column in one of the three tables, or is it the same as the ROW_NUM you have commented out? If it is the latter, the code would not work. You cannot use an alias from the SELECT list in the WHERE clause of the same query.
See the code below - this is perhaps one way of eliminating duplicates. The ORDER BY clause allows you to pick which of the duplicate rows you want to pick.
SELECT ADDR.IND_ID AS IND_ID ,
ADDR.ADDRESS_ID AS ADDRESS_ID ,
ADDR_STG.X_SPECIAL_INFO AS X_SPECIAL_INFO ,
ADDR.STATE_CODE AS STATE_CODE ,
ROW_NUMBER() OVER ( PARTITION BY ADDR.ADDRESS_ID, ADDR.IND_ID ORDER BY ADDR.ADDRESS_ID, ADDR.IND_ID ) AS ROW_NUM
FROM ADDR_PER ADDR_STG WITH ( NOLOCK )
CROSS APPLY
(
SELECT TOP (1) * FROM IND_XREF XREF WHERE ADDR_STG.PER_ID = XREF.SOURCE_SYSTEM_KEY
ORDER BY Something
) XREF
--INNER JOIN IND_XREF XREF WITH ( NOLOCK ) ON ( ADDR_STG.PER_ID = XREF.SOURCE_SYSTEM_KEY )
CROSS APPLY
(
SELECT TOP (1) * FROM TB_ADDRESS ADDR WHERE XREF.IND_ID = ADDR.IND_ID
ORDER BY Something
) ADDR
--INNER JOIN TB_ADDRESS ADDR WITH ( NOLOCK ) ON ( XREF.IND_ID = ADDR.IND_ID )
As an aside, I would recommend not using NOLOCK hint. It can cause problems without offering much of the perceived advantages.