CODEWITHSUNDEEP
sqlsql-server
Ratha
Ratha

Reputation: 9692

Select an id from a group sql which returns duplicate records?

I have to select CompanyId column only from the following SQL;

select CompanyId,
           row_number() over (partition by [GradeName] order by [TankNumber] ) rn
    from [Data_DB].[dbo].[Company]  where CompanyCode='ASAAA'

In the SQL, I try to figure out duplicate records, and from another table i want to delete some records based on the CompanyId from above query.

that is;

delete from [[dbo].ObservationData 
where CompanyId in (select CompanyId,
               row_number() over (partition by [GradeName] order by [TankNumber] ) rn
        from [Data_DB].[dbo].[Company]  where CompanyCode='ASAAA')

How can I modify above query?

Upvotes: 1

Views: 315

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521579

Assuming you don't care which duplicate gets retained or deleted, you may try using a deletable CTE here:

WITH cte AS (
    SELECT *,
       ROW_NUMBER() OVER (PARTITION BY [GradeName] ORDER BY [TankNumber]) rn
    FROM [Data_DB].[dbo].[Company]
    WHERE CompanyCode = 'ASAAA'
)

DELETE
FROM cte
WHERE rn > 1;

This answer arbitrarily retains the "first" duplicate, with first being defined as the record with the earliest row number.

Upvotes: 3

Swathi8038
Swathi8038

Reputation: 96

delete from [[dbo].ObservationData 
where CompanyId in (select CompanyId from (select CompanyId,
               row_number() over (partition by [GradeName] order by [TankNumber] ) rn
        from [Datat_DB].[dbo].[Company]  where CompanyCode='ASAAA') a where rn > 1 ;

Upvotes: 0

Related Questions