Delete repeated Ids from Table – Performance Improvement.

CREATE TABLE dbo.Data( FriendlyFunctionCode INT, MemberFirmId VARCHAR(100),FunctionLevel3Desc VARCHAR(100))
GO
INSERT INTO dbo.Data
VALUES(1,’Value1′,’Value2′)
,(1,’Value2′,’Value3′)
,(2,’Value4′,’Value5′)

SELECT * FROM dbo.Data

;WITH CTE
AS (
SELECT FriendlyFunctionCode,MemberFirmId,
FunctionLevel3Desc,
Row_number()OVER (PARTITION BY FriendlyFunctionCode ORDER BY (SELECT 1) ) AS Rn

FROM dbo.Data)

SELECT * FROM CTE WHERE Rn = 1

DROP TABLE Data

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s