Click here to Skip to main content
15,948,155 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
Hi,

I have a table table1 having the details as below

RowID	ID	Test_BEGDT	Test_ENDDT	Test1_BEGDT	Test1_ENDDT
1	220041	4/13/1998	3/15/2020	1/1/2014	12/31/2020
2	220041	4/13/1998	12/31/9999	1/1/2014	12/31/2020
3	220041	4/13/1998	12/31/9999	1/1/2015	12/31/9999
4	220041	4/13/1998	12/31/9999	12/31/9999	12/31/9999


i need to delete all the duplicates for the ID and want only a single record in the table.
The Dates vary for each ID and are not constant.

i tried using the below code but the wrong record is remaining.
i need the record having TEST1_BEGDT=1/1/2015 should be remained after removing all the other duplicates because it is my latest record

SQL
WITH CTE(RowNumber, Sequence, ID ,Test_BEGDT, Test_ENDDT, Test1_BEGDT, Test1_ENDDT ) AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ID, TEST_BEGDT, TEST1_BEGDT order by ID, TEST_BEGDT, TEST1_BEGDT ) AS RowNumber,
ROW_NUMBER() over (PARTITION BY id order by id) as Sequence
, ID
, TEST_BEGDT
,TEST_ENDDT
, TEST1_BEGDT
, TEST1_ENDDT
FROM table1 tbl
)
--select * from CTE 
delete from CTE where Sequence > 1


after running the above query my result set looks like below from which allthe records greater than sequence 1 are being deleted

Row sequence  id     test_begdt test_enddt test1_begdt test1_enddt
1   1        220041  1998-04-13 2020-03-15 2014-01-01  2020-12-31 
2   2        220041  1998-04-13 9999-12-31 2014-01-01  2020-12-31 
1   3        220041  1998-04-13 9999-12-31 2015-01-01  9999-12-31 
1   4        220041  1998-04-13 9999-12-31 9999-12-31  9999-12-31 


i have to delete the records 1,2,4 and make sure 3 record is present in the table
since the 1 and 2 records are duplicates and 4th record have default test1_begdt date(9999-12-31)

please suggest

when i try and
Posted
Updated 17-Dec-14 4:05am
v4
Comments
Sergey Alexandrovich Kryukov 17-Dec-14 11:15am    
Why not taking care of not adding duplicated in first place?
—SA

1 solution

Try this

SQL
WITH CTE(RowNumber, Sequence, ID ,Test_BEGDT, Test_ENDDT, Test1_BEGDT, Test1_ENDDT ) AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ID order by ID, TEST_BEGDT, TEST1_BEGDT desc) AS RowNumber,
ROW_NUMBER() over (PARTITION BY id order by id) as Sequence
, ID
, TEST_BEGDT
,TEST_ENDDT
, TEST1_BEGDT
, TEST1_ENDDT
FROM table1 tbl
)
delete from CTE where RowNumber > 1
 
Share this answer
 
Comments
anupama962010 17-Dec-14 12:11pm    
its not working as per the above query i am getting the record with default date
Shweta N Mishra 18-Dec-14 3:27am    
you could replace the Default date while creating the Row_Number with lower date as below.

WITH CTE(RowNumber, Sequence, ID ,Test_BEGDT, Test_ENDDT, Test1_BEGDT, Test1_ENDDT ) AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ID order by ID, TEST_BEGDT, Replace(TEST1_BEGDT,'Dec 31 9999','Jan 01 1900') desc) AS RowNumber,
ROW_NUMBER() over (PARTITION BY id order by id) as Sequence
, ID
, TEST_BEGDT
,TEST_ENDDT
, TEST1_BEGDT
, TEST1_ENDDT
FROM table1 tbl
)
delete from CTE where RowNumber > 1

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900