December 16, 2014 at 6:21 am
Hi folks,
Need your help in below query.
My SQL Table has multiple records on which i have to do Dedup. Now the problem is i have to do dedup on Multiple columns (SRC, IP_REF ).
The condition is first SP should check if same SRC is repeating if yes then do dedup on IP_ref and vice versa.
since i want to avoid while loop ; something like Partitions, dense rank or rank can work?
Please suggest.
Thanks
December 16, 2014 at 7:23 am
Try this:
--== TEST DATA ==--
IF NOT OBJECT_ID('tempdb..#Temp') IS NULL DROP TABLE #Temp;
CREATE TABLE #Temp (SRC Int, IP_REF Int);
INSERT #Temp VALUES (1, 1);
INSERT #Temp VALUES (1, 1);
INSERT #Temp VALUES (1, 1);
INSERT #Temp VALUES (1, 2);
INSERT #Temp VALUES (2, 2);
INSERT #Temp VALUES (2, 2);
INSERT #Temp VALUES (2, 3);
INSERT #Temp VALUES (3, 1);
INSERT #Temp VALUES (3, 2);
SELECT * FROM #Temp;
--== SOLUTION ==--
WITH CTE AS
(
SELECT *
,RN = ROW_NUMBER() OVER (PARTITION BY SRC, IP_REF ORDER BY SRC)
FROM #Temp
)
DELETE FROM CTE WHERE RN > 1
SELECT * FROM #Temp;
Deleting from a single-table CTE deletes from the underlying table.
December 16, 2014 at 7:39 am
Quick though, laurie-789651's solution is good but given the requirements of
The condition is first SP should check if same SRC is repeating if yes then do dedup on IP_ref and vice versa.
does it mean that only one instance of each SRC value can exist and only one instance of IP_ref can exist?
😎
Using laurie-789651's sample data set, a distinct combination of values would include multiple instances of each value
SRC IP_REF
---- -------
1 1
1 2
2 2
2 3
3 1
3 2
If I understand the requirements correctly, the result set should be
SRC IP_REF
---- -------
1 1
2 3
3 2
December 16, 2014 at 10:40 pm
Thanks Folks!!
it works! 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply