1

I need to remove the duplication of rows based on column to have unique data in this column, additionally I need another condition when the value equal specific information.

In my example I want to filter by TagName to avoid duplication plus I need the value of Alarmstate = 'UNACK_ALM'.

Please refer to the pictures for more clarification, I did it using Excel but I need to write it in SQL view.

Thanks for coopearation. enter image description here enter image description here

1

Use Partitioning function of SQL Server to get distinct values based on column TagName

-- Create Table to test data 
CREATE TABLE OriginalTable
(
Eventstamp DATETIME,
AlarmState VARCHAR(200),
TagName VARCHAR(200),
[Description] VARCHAR(500),
Area VARCHAR(200)
)

--Insert dummy Data 
INSERT INTO OriginalTable
SELECT '10/30/2014 21:39','UNACK_ALM','Alarm04','Aarm04 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','UNACK_ALM','Alarm03','Aarm03 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','UNACK_ALM','Alarm01','Aarm01 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','UNACK_ALM','Alarm02','Aarm02 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','ACK_RTN','Alarm03','Aarm03 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','ACK','Alarm03','Aarm03 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','ACK_RTN','Alarm01','Aarm01 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','ACK','Alarm01','Aarm01 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','ACK_RTN','Alarm02','Aarm02 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','ACK','Alarm02','Aarm02 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','ACK_RTN','Alarm04','Aarm04 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','ACK','Alarm04','Aarm04 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','UNACK_ALM','Alarm04','Aarm04 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','UNACK_ALM','Alarm03','Aarm03 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','UNACK_ALM','Alarm02','Aarm02 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','UNACK_ALM','Alarm01','Aarm01 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','ACK_RTN','Alarm04','Aarm04 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','ACK','Alarm04','Aarm04 ON','$System'
UNION ALL 
SELECT '10/30/2014 21:39','UNACK_ALM','Alarm04','Aarm04 ON','$System'
UNION ALL
SELECT '10/30/2014 21:39','ACK_TRN','Alarm03','Aarm03 ON','$System'
UNION ALL
SELECT '10/30/2014 21:39','ACK','Alarm03','Aarm03 ON','$System'


--- Query to test data 
SELECT * 
FROM OriginalTable
WHERE Alarmstate = 'UNACK_ALM'


-----Create view for distinct values
GO

SELECT  *
FROM    (                              --- Partition data on column TagName
          SELECT    ROW_NUMBER() OVER ( PARTITION BY TagName ORDER BY eventStamp) RN,*  
          FROM      OriginalTable
          WHERE     Alarmstate = 'UNACK_ALM' --- apply filter for specific Alarmstate
        ) alarms
WHERE   RN = 1 --- Filter data for distinct values
3

To remove duplicate rows, use a CTE and partition using the data you want to check for duplicates to assign a row_number against. Then remove any where the row_number > 1 (removing multiple duplicates)

    --REMOVE DUPLICATES
    WITH CTE AS
    (SELECT ROW_NUMBER() OVER 
(PARTITION BY column_tocheck_duplication_against ORDER BY ( SELECT 0)) RN
    FROM TableName)
    DELETE FROM CTE WHERE RN > 1
  • Would you please write the code based on my example in the attached pictures? because I am not familiar with SQL I am just trying to build new view in SQL to link it to excel – Amjad Nov 10 '14 at 17:54
  • I cannot see your pictures – Mike Nov 11 '14 at 8:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.